SQL进阶提升(xml合并拆分详细实例)

      今天在网上看到一个关于sql的疑难问题是这样的：

一个数据表里有一个varchar字段

varchar类型的一个字段包含这样的数据33,444,5555
要求把33加89，444加899，5555加8999
其实就是两位数字，三位数字，四位数字中间用逗号隔开这种格式
但是可能会没有第三位，或者第第二，第三位数都没有，只有第一位数。

创建临时表插入数据：

 

create table #T
(
id int identity(1,1) primary key,
data nvarchar(20)
)

 insert into #T
 values('33,444,5555')

 declare @count int
 set @count=1
 while(@count<1000)
 begin
 insert into #T
 values('12,123')
 insert into #T
 values('33,444,5555')
 insert into #T
 values('11')
set @count=@count+1
end
select * from #T

现在用sql2005 xml拆分聚合来进行update，当然可以用简单的sql update比如：

 

update #T set data=(
case when len(data)=2 then cast(left(data,2)+89 as varchar)
     when len(data)=6 then cast(left(data,2)+89 as varchar)+','+cast(substring(data,4,3)+899 as varchar)
     when len(data)=11 then cast(left(data,2)+89 as varchar)+','+cast(substring(data,4,3)+899 as varchar)+','+cast(substring(data,8,4)+8999 as varchar)
     else data
     end
)

select * from #T

 

xml 来update

拆分：

 

--拆分
SELECT A.id, B.data,len(B.data) as len into #TT
FROM(
    SELECT id, [data] = CONVERT(xml,'<root><v>' + REPLACE([data], ',', '</v><v>') + '</v></root>') FROM #T
)A
OUTER APPLY(
    SELECT data = N.v.value('.', 'varchar(100)') FROM A.[data].nodes('/root/v') N(v)
)B

 

 

更新：

 

select * from #TT
--更新
update  #TT set data=data+(
case when len=2 then 899 
     when len=3 then 5555 
     when len=4 then 8999 
     else 0
end
)

合并：

 

update #T set data=
(select top 1 data
from
(
select A.id,b.data
from
(select distinct id from #TT)as a
outer apply
(
select stuff(replace(replace((select data from #TT where id=a.id for xml auto),'<_x0023_TT data="',','),'"/>',''),1,1,'') 
as data
)as b
)C 
where C.id=#T.id)

select * from #T

drop table #TT
drop table #T

解释：replace((select data from #TT where id=a.id for xml auto),'<_x0023_TT data="',',')

在select data from #TT,(select distinct id from #TT)as a where #TT.id=a.id for xml auto
我们可以看到sql查询的结果如下，那么通过替代把xml属于性标签替代成，号，最后的stuff是去掉第一个逗号。

点开查询结果是一个xml文档

 

 

 

 

这里主要演示怎么进行聚合和拆分，如果是大量数据不介意这么用， 这主要应用在查询方面。