MyException - 我的异常网
当前位置:我的异常网» Sql Server » sql疑窦查询

sql疑窦查询

www.MyException.Cn  网友分享于:2013-02-20  浏览:2次
sql疑难查询

id     attachment_id                  attachment_name
1     1302_1,1302_2,1333_3,           一.doc*二.jpg*三.psd*


我想查出
id      mc
1       1302/1.一.doc
1       1302/2.二.jpg
1       1333/3.三.psd      

------解决方案--------------------
好难,我用substring如下
select Id,substring(attachment_id,1,4)+'/'+SUBSTRING(attachment_id,13,1)+'.'+substring(attachment_name,1,6) mc from Test
union all
select Id,substring(attachment_id,8,4)+'/'+SUBSTRING(attachment_id,6,1)+'.'+substring(attachment_name,8,5) mc from Test
union all
select Id,substring(attachment_id,15,4)+'/'+SUBSTRING(attachment_id,20,1)+'.'+substring(attachment_name,14,5) mc from Test


------解决方案--------------------
with tb(id,attachment_id,attachment_name)
as(
select 1,'1302_1,1302_2,1333_3','一.doc*二.jpg*三.psd*'
),tb1
as(
select t1.id,attachment_id=replace(substring(attachment_id,number+1,charindex(',',attachment_id,number)),',',''),row=row_number()over(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(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

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有