分拆字符串并统计

这个是参考了小梁MM写的

http://topic.youkuaiyun.com/u/20090207/11/fba1c36c-e47d-42e4-85c4-c95e3da97dee.html

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