请问一个分组查询的问题
请问一个比较烦琐的分组查询语句，数据格式如下所示
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，谅解谅解，呵呵。
[解决办法]
自定义函数 + 游标
处理
[解决办法]
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
[解决办法]
--建立测试环境
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
*/
[解决办法]
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
[解决办法]
强,收藏先
