请问一个分组查询的问题

Question

请问一个比较烦琐的分组查询语句，数据格式如下所示
FlowID        DeptID                      DTime
4 ,,1,13,13,1,2,               2007-07
6 ,2,1,1,1,1,1,1,17,               2007-07
29 ,2,2,1,1,13,13,13,1,1,2,      2007-07
30 ,2,1,13,13,1,               2007-08
31 ,,1,17,17,1,17,17,1,1,,      2007-08
33 ,2,1,4,1,1,2,               2007-08
37 ,2,2,2,2,3,2,               2007-08
54 ,2,2,2,3,2,               2007-08我想检索成这样的格式，把DeptID分解开（去掉重复的）
FlowID       DeptID        DTime
4              1          2007-07
4              2          2007-07
4             13          2007-07
6              1          2007-07
..................或者直接统计出来
DeptID        Count           DTime
1              3             2007-07
1              3             2007-08
2              3             2007-07
2              4             2007-08
.....................................请高手帮忙，谢谢，如果分不够可以另外开帖，穿着裤衩只能一贴最多只能100，谅解谅解，呵呵。

Answer 1

declare @t table (
FlowID        int,
DeptID        varchar(30),              
DTime      varchar(10)
)
insert @t select
4, ',,1,13,13,1,2,'    ,           '2007-07'
union all select
6, ',2,1,1,1,1,1,1,17,'    ,           '2007-07'
union all select
29, ',2,2,1,1,13,13,13,1,1,2,',      '2007-07'
union all select
30, ',2,1,13,13,1,'           ,    '2007-08'
union all select
31, ',,1,17,17,1,17,17,1,1,,',      '2007-08'
union all select
33, ',2,1,4,1,1,2,'          ,     '2007-08'
union all select
37, ',2,2,2,2,3,2,'          ,     '2007-08'
union all select
54, ',2,2,2,3,2,'          ,     '2007-08'
declare @r table (
FlowID        int,
DeptID        int,              
DTime      varchar(10)
)while exists (
select 1 from @t where replace(deptid,',','')<>''
)
begin
insert @r
select FlowID,
substring(deptid,PATINDEX('%,[0-9]%',deptid)+1,charindex(',',deptid,PATINDEX('%,[0-9]%',deptid)+1)-PATINDEX('%,[0-9]%',deptid)-1) ,
DTime
from @t
where replace(deptid,',','')<>''
update @t
set deptid=stuff(deptid,1,charindex(',',deptid,PATINDEX('%,[0-9]%',deptid)+1)-1,'')
where replace(deptid,',','')<>''end
--第一个结果
select distinct * from @r
--第二个结果
select DeptID,Count(*) as [Count],DTime
from (select distinct * from @r) as t
group by DeptID,DTime

Answer 2

--建立测试环境
create table #tb(FlowID int,DeptID varchar(25),DTime varchar(10))
insert #tb(FlowID,DeptID,DTime)
select '4',',,1,13,13,1,2,','2007-07' union all
select '6',',2,1,1,1,1,1,1,17,','2007-07' union all
select '29',',2,2,1,1,13,13,13,1,1,2,','2007-07' union all
select '30',',2,1,13,13,1,','2007-08' union all
select '31',',,1,17,17,1,17,17,1,1,,','2007-08' union all
select '33',',2,1,4,1,1,2,','2007-08' union all
select '37',',2,2,2,2,3,2,','2007-08' union all
select '54',',2,2,2,3,2,','2007-08'
go
--执行测试语句
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.#tb_splitSTR
FROM syscolumns a,syscolumns b
GO
SELECT distinct FlowID,DTime,DeptID=CAST(SUBSTRING(DeptID,ID,CHARINDEX(',',DeptID+',',ID)-ID) as varchar(25))
FROM #tb_splitSTR,#tb
WHERE ID<=LEN(DeptID+'a') 
      AND CHARINDEX(',',','+DeptID,ID)=ID
      and CAST(SUBSTRING(DeptID,ID,CHARINDEX(',',DeptID+',',ID)-ID) as varchar(100)) <> ''
order by FlowID,DTime,DeptID
GO
SELECT  DTime,CAST(SUBSTRING(DeptID,ID,CHARINDEX(',',DeptID+',',ID)-ID) as varchar(100))as DeptID
      ,count(distinct FlowID) as [count]
FROM #tb_splitSTR,#tb
WHERE ID<=LEN(DeptID+'a') 
      AND CHARINDEX(',',','+DeptID,ID)=ID
      and CAST(SUBSTRING(DeptID,ID,CHARINDEX(',',DeptID+',',ID)-ID) as varchar(100)) <> ''
group by CAST(SUBSTRING(DeptID,ID,CHARINDEX(',',DeptID+',',ID)-ID) as varchar(100)),DTime
order by DeptID,DTime,[count]
--删除测试环境
drop table #tb,#tb_splitSTR
go
/*--测试结果
FlowID      DTime      DeptID                    
----------- ---------- ------------------------- 
4           2007-07    1
4           2007-07    13
4           2007-07    2
6           2007-07    1
6           2007-07    17
6           2007-07    2
29          2007-07    1
29          2007-07    13
29          2007-07    2
30          2007-08    1
30          2007-08    13
30          2007-08    2
31          2007-08    1
31          2007-08    17
33          2007-08    1
33          2007-08    2
33          2007-08    4
37          2007-08    2
37          2007-08    3
54          2007-08    2
54          2007-08    3(21 row(s) affected)DTime      DeptID     count       
---------- ---------- ----------- 
2007-07     1     3
2007-08     1     3
2007-07     13    2
2007-08     13    1
2007-07     17    1
2007-08     17    1
2007-07     2     3
2007-08     2     4
2007-08     3     2
2007-08     4     1*/

Answer 3

--结果
FlowID      DeptID      DTime      
----------- ----------- ---------- 
4           1           2007-07
4           2           2007-07
4           13          2007-07
6           1           2007-07
6           2           2007-07
6           17          2007-07
29          1           2007-07
29          2           2007-07
29          13          2007-07
30          1           2007-08
30          2           2007-08
30          13          2007-08
31          1           2007-08
31          17          2007-08
33          1           2007-08
33          2           2007-08
33          4           2007-08
37          2           2007-08
37          3           2007-08
54          2           2007-08
54          3           2007-08（所影响的行数为 21 行）DeptID      Count       DTime      
----------- ----------- ---------- 
1           3           2007-07
2           3           2007-07
13          2           2007-07
17          1           2007-07
1           3           2007-08
2           4           2007-08
3           2           2007-08
4           1           2007-08
13          1           2007-08
17          1           2007-08（所影响的行数为 10 行）

Answer 4

改成存储过程create proc pr_test
as
set nocount on
declare @t table (
FlowID        int,
DeptID        varchar(30),              
DTime      varchar(10)
)
insert @t select FlowID,DeptID,DTime from tb              --tb是你自己的表declare @r table (
FlowID        int,
DeptID        int,              
DTime      varchar(10)
)while exists (
select 1 from @t where replace(deptid,',','')<>''
)
begin
insert @r
select FlowID,
substring(deptid,PATINDEX('%,[0-9]%',deptid)+1,charindex(',',deptid,PATINDEX('%,[0-9]%',deptid)+1)-PATINDEX('%,[0-9]%',deptid)-1) ,
DTime
from @t
where replace(deptid,',','')<>''
update @t
set deptid=stuff(deptid,1,charindex(',',deptid,PATINDEX('%,[0-9]%',deptid)+1)-1,'')
where replace(deptid,',','')<>''end
--第一个结果
select distinct * from @r
--第二个结果
select DeptID,Count(*) as [Count],DTime
from (select distinct * from @r) as t
group by DeptID,DTimego--运行
exec pr_test

Answer 5

create table #c(FlowID int,DeptID varchar(200),DTime varchar(7))
FlowID        DeptID                      DTime  insert #c select 
4, ',,1,13,13,1,2,'      , '2007-07' insert #c select
6, ',2,1,1,1,1,1,1,17,'      , '2007-07' insert #c select
29, ',2,2,1,1,13,13,13,1,1,2,'   , '2007-07' insert #c select
30, ',2,1,13,13,1,'              , '2007-08' insert #c select
31, ',,1,17,17,1,17,17,1,1,,'    , '2007-08' insert #c select
33, ',2,1,4,1,1,2,'              , '2007-08' insert #c select
37, ',2,2,2,2,3,2,'              , '2007-08' insert #c select
54, ',2,2,2,3,2,'              , '2007-08'
-----------------------------------------------------------------
create table #cc(FlowID int,DeptID varchar(200),DTime varchar(7),id int identity(1,1))
declare cur_a cursor for select stuff(DeptID,1,1,''),FlowID,DTime from #c
open cur_a
declare @a varchar(200),@b int,@c varchar(7),@i int
fetch next from cur_a into @a,@b,@c
while(@@fetch_status=0)
begin
   set @i=charindex(',',@a)
   while @i<>0
   begin
      insert #cc select @b,left(@a,@i-1),@c
      set @a=stuff(@a,1,@i,'')
      set @i=charindex(',',@a)
   end
   fetch next from cur_a into @a,@b,@c
end
close cur_a
deallocate cur_a
----------
delete #cc from #cc a where DeptID='' or exists (select 0 from #cc b where a.FlowID=b.FlowID and a.DeptID=b.DeptID and a.DTime=b.DTime and b.id>a.id) 
select * from #cc---第一个统计
select DeptID,count(DeptID)[count],DTime from #cc group by DeptID,DTime order by cast(DeptID as int),DTime--第2个统计
drop table #cc