字符串整理系列(1)
（1）字符串分拆并统计
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) select @Len set rowcount @Len select ID=identity(int,1,1) into #TT from dbo.syscolumns A,dbo.syscolumns B set rowcount 0 ;with hgo as ( select b.id, number=substring(col,A.id,charindex(',',col+',',A.id)-A.id) from #TT A join #tb b on substring(','+col,A.id,1)=',' ) select number,count(distinct id) [count],count(number) [number] from hgo group by number create table #tb( id int, col varchar(50), num int)insert into #tb select 1,'aa,bb,cc',10union all select 2,'aa,aa,bb',20union all select 3,'aa,aa,bb',20union all select 4,'dd,ccc,c',30union all select 5,'ddaa,ccc',40union all select 6,'eee,ee,c',50--SQL查询如下：;WITH Numbers AS( SELECT TOP(50) Seq=ROW_NUMBER() OVER(ORDER BY [object_id]) FROM sys.objects),Liang AS( SELECT A.Seq, B.id, SUBSTRING(B.col,A.Seq,CHARINDEX(',',B.col+',',A.Seq)-A.Seq) AS v FROM Numbers AS A JOIN #tb AS B ON SUBSTRING(','+B.col,A.Seq,1)=',')SELECT v AS data, COUNT(DISTINCT id) AS [count], COUNT(*) AS numbersFROM LiangGROUP BY vDROP TABLE #tb