分组统计问题.

Question

create TABLE #A(sName varchar(20),tWeek int,icount int ) 
insert into #A select '张三,李四',13,1
insert into #A select '张三,李四',13,1
insert into #A select '张三,李四',13,1
insert into #A select '张三',13,1
insert into #A select '张三',13,1
insert into #A select '李四',13,1
insert into #A select '张三,小二',13,1
insert into #A select '张三,小二',13,1
insert into #A select '小二',13,1
insert into #A select '小二,李四',13,1
insert into #A select '张三,李四',14,1
insert into #A select '李四',14,1
insert into #A select '张三',14,1
想通过sName 和 tWeek 分组 统计 iCount的数量.
要求:如'张三,李四'要分开统计到张三和李四的数量中.
得到如下结果.TKS!李四 7
小二 4
张三 9

Answer 1

--参考
拆分表:--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
    a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) 
from 
    Tab a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=','
SQL2005用Xml:select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

Answer 2

create TABLE #A(sName varchar(20),tWeek int,icount int ) 
insert into #A select '张三,李四',13,1
insert into #A select '张三,李四',13,1
insert into #A select '张三,李四',13,1
insert into #A select '张三',13,1
insert into #A select '张三',13,1
insert into #A select '李四',13,1
insert into #A select '张三,小二',13,1
insert into #A select '张三,小二',13,1
insert into #A select '小二',13,1
insert into #A select '小二,李四',13,1
insert into #A select '张三,李四',14,1
insert into #A select '李四',14,1
insert into #A select '张三',14,1SELECT SNAME,COUNT(1)NUM FROM 
(
SELECT PARSENAME(REPLACE(sName,',','.'),1)sName ,tWeek ,ICOUNT FROM #A
UNION ALL
SELECT PARSENAME(REPLACE(sName,',','.'),2)sName ,tWeek ,ICOUNT FROM #A
)T 
WHERE SNAME IS NOT NULL GROUP BY SNAMESNAME                                                                                                                            NUM         
-------------------------------------------------------------------------------------------------------------------------------- ----------- 
李四                                                                                                                               7
小二                                                                                                                               4
张三                                                                                                                               9（所影响的行数为 3 行）

Answer 3

set nocount on
create TABLE #A(sName nvarchar(20),tWeek int,icount int ) 
insert into #A select N'张三,李四',13,1
insert into #A select N'张三,李四',13,1
insert into #A select N'张三,李四',13,1
insert into #A select N'张三',13,1
insert into #A select N'张三',13,1
insert into #A select N'李四',13,1
insert into #A select N'张三,小二',13,1
insert into #A select N'张三,小二',13,1
insert into #A select N'小二',13,1
insert into #A select N'小二,李四',13,1
insert into #A select N'张三,李四',14,1
insert into #A select N'李四',14,1
insert into #A select N'张三',14,1
SELECT 
Sname=SUBSTRING(a.sName,number,CHARINDEX(',',a.sName+',',number)-b.number),
COUNT(*)数量
FROM #A a JOIN master..spt_values b
ON b.type='p' AND CHARINDEX(',',','+a.sName,number)=number
GROUP BY 
SUBSTRING(a.sName,number,CHARINDEX(',',a.sName+',',number)-b.number)
/*
Sname                数量
-------------------- -----------
小二                   4
李四                   7
张三                   9*/
DROP TABLE #a

Answer 4

--先执行该字符串分割函数：
--SQL Server Split函数
--Author:zc_0101 
--说明：
--支持分割符多字节
--使用方法 
--Select * FROM DBO.F_SQLSERVER_SPLIT('1203401230105045','0')    
--select * from DBO.F_SQLSERVER_SPLIT('abc1234a12348991234','1234')    
 CREATE FUNCTION F_SQLSERVER_SPLIT(@Long_str varchar(8000),@split_str varchar(100))    
RETURNS  @tmp TABLE(        
    ID          inT     IDENTITY PRIMARY KEY,      
    short_str   varchar(8000)    
)    
AS   
BEGIN   
    DECLARE @long_str_Tmp varchar(8000),@short_str varchar(8000),@split_str_length int   
    SET @split_str_length = LEN(@split_str)    
    IF CHARINDEX(@split_str,@Long_str)=1 
         SET @long_str_Tmp=SUBSTRING(@Long_str,@split_str_length+1,LEN(@Long_str)-@split_str_length)
    ELSE
         SET @long_str_Tmp=@Long_str
    IF CHARINDEX(REVERSE(@split_str),REVERSE(@long_str_Tmp))>1    
        SET @long_str_Tmp=@long_str_Tmp+@split_str    
    ELSE   
        SET @long_str_Tmp=@long_str_Tmp    
IF CHARINDEX(@split_str,@long_str_Tmp)=0
Insert INTO @tmp select @long_str_Tmp 
ELSE
BEGIN
WHILE CHARINDEX(@split_str,@long_str_Tmp)>0    
BEGIN   
SET @short_str=SUBSTRING(@long_str_Tmp,1,CHARINDEX(@split_str,@long_str_Tmp)-1)    
DECLARE @long_str_Tmp_LEN INT,@split_str_Position_END int   
SET @long_str_Tmp_LEN = LEN(@long_str_Tmp)    
SET @split_str_Position_END = LEN(@short_str)+@split_str_length    
SET @long_str_Tmp=REVERSE(SUBSTRING(REVERSE(@long_str_Tmp),1,@long_str_Tmp_LEN-@split_str_Position_END))
IF @short_str<>'' Insert INTO @tmp select @short_str    
END   
END
    RETURN     
END --查询开始：
select sname,count(*) cnt from (
select b.short_str sName,tWeek,icount from #a
cross apply
(
 select * from dbo.F_SQLSERVER_SPLIT(sName,',')
)b
)c group by sname /*
sname cnt
李四 7
小二 4
张三 9
*/

Answer 5

--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
create TABLE tb(sName varchar(20),tWeek int,icount int ) 
insert into tb select '张三,李四',13,1
insert into tb select '张三,李四',13,1
insert into tb select '张三,李四',13,1
insert into tb select '张三',13,1
insert into tb select '张三',13,1
insert into tb select '李四',13,1
insert into tb select '张三,小二',13,1
insert into tb select '张三,小二',13,1
insert into tb select '小二',13,1
insert into tb select '小二,李四',13,1
insert into tb select '张三,李四',14,1
insert into tb select '李四',14,1
insert into tb select '张三',14,1GO
Select sName=substring(a.sName,b.number,charindex(',',a.sName+',',b.number)-b.number),
icount=SUM(icount) 
from tb a 
join master..spt_values  b 
ON B.type='p'
where substring(','+a.sName,b.number,1)=','
group by substring(a.sName,b.number,charindex(',',a.sName+',',b.number)-b.number)
/*
sName                icount
-------------------- -----------
张三                   9
小二                   4
李四                   7(3 行受影响)
*/

Answer 6

;with cte as 
(select sName=cast(left(sName,charindex(',',sName+',')-1) as nvarchar(100))
,Split=cast(stuff(sName+',',1,charindex(',',sName+','),'') as nvarchar(100))
,tWeek,icount
from #A
union all
select sName=cast(left(Split,charindex(',',Split)-1) as nvarchar(100))
,Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) 
,tWeek,icount
from cte where split>''
) 
select sName,count(*) from cte group by sNameoption (MAXRECURSION 0)sName                                                                                                           
---------------------------------------------------------------------------------------------------- -----------
李四                                                                                                             7
小二                                                                                                             4
张三                                                                                                             9(3 行受影响)CTE