分拆字符串求和

最新推荐文章于 2024-07-03 03:35:32 发布

最新推荐文章于 2024-07-03 03:35:32 发布 · 148 阅读

本文介绍了一种使用T-SQL实现的分拆字符串并按指定字段进行求和的方法。通过创建临时表并插入多条记录，利用递归CTE和字符串函数实现了对字符串中特定元素的拆分及数值字段的汇总。此方法适用于需要处理逗号分隔值并进行聚合运算的场景。

--分拆字符串求和 create table #tb ( id int, col varchar(50), num int ) insert into #tb select 1,'aa,bb,cc',10 union all select 2,'aa,aa,bb',20 union all select 3,'aa,aa,bb',20 union all select 4,'dd,ccc,c',30 union all select 5,'ddaa,ccc',40 union all select 6,'eee,ee,c',50 declare @len int select top 1 @len=len(col)+1 from #tb order by len(col) desc set rowcount @len select id=identity(int,1,1) into # from dbo.syscolumns A,dbo.syscolumns B set rowcount 0 ;with hgo as ( select distinct data=substring(A.col,b.id,charindex(',',A.col+',',b.id)-b.id), A.num,b.id from #tb A,# b where substring(','+A.col,b.id,1)=',' ) select data,sum(num) from hgo group by data