sql疑难查询

Question

表
id     attachment_id                  attachment_name
1     1302_1,1302_2,1333_3,           一.doc*二.jpg*三.psd*
2     1304_1,                         内容.doc*我想查出
            mc
       1302/1.             一.doc
       1302/2.             二.jpg
       1333/3.             三.psd
       1304/1.            内容.doc
            

Answer 1

with tb(id,attachment_id,attachment_name)
as(
select 1,'1302_1,1302_2,1333_3','一.doc*二.jpg*三.psd*'
union all
select 2,'1304_1','内容.doc*'
),tb1
as(
select t1.id,attachment_id=replace(substring(attachment_id,number+1,charindex(',',attachment_id+',',number)),',',''),row=row_number()over(partition by id order by getdate()) from tb t1,master..spt_values where type='p' and (substring(attachment_id,number,1)=',' or number=0)
),
tb2
as(
select t1.id,attachment_name=replace(substring(attachment_name,number+1,charindex('*',attachment_name,number)),'*',''),row=row_number()over(partition by id order by getdate()) from tb t1,master..spt_values where type='p' and (substring(attachment_name,number,1)='*' or number=0)
)
select tb1.id,replace(tb1.attachment_id,'_','/')+'.'+tb2.attachment_name from tb1 join tb2 on tb1.id=tb2.id and tb1.row=tb2.row and tb1.id=tb2.id

Answer 2

表
id     attachment_id                  attachment_name
1     1302_1,1302_2,1333_3,           一.doc*二.jpg*三.psd*
2     1304_1,                         内容.doc*我想查出两个字段
            mc                 md
       1302/1.             一.doc
       1302/2.             二.jpg
       1333/3.             三.psd
       1304/1.            内容.doc

Answer 3

select tb1.id,mc=replace(tb1.attachment_id,'_','/'),md=tb2.attachment_name from tb1 join tb2 on tb1.id=tb2.id and tb1.row=tb2.row and tb1.id=tb2.id