字符串整理系列(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