如数据源 2006-03-05为第1天 2006-04-08为第二天id ani agentID type datetime
----------- ------------ ---- ---- -------------------------------
31387 16062567655 201 in 2006-03-05 08:19:02.000
31388 50380088 201 in 2006-03-05 08:23:30.000
31389 13916089473 202 out 2006-03-05 08:25:53.00031390 13524615926 202 out 2006-03-05 09:27:23.000
31423 13381754439 201 out 2006-03-05 09:38:43.000
31424 65039950 202 in 2006-03-05 09:40:16.000
31504 64870891 201 out 2006-04-08 09:09:22.000
31505 64870871 201 in 2006-04-08 09:17:13.000
31506 64870891 202 in 2006-04-08 09:36:07.000
31507 64870851 201 out 2006-04-08 09:44:56.000
31508 64870841 202 in 2006-04-08 09:55:46.00031442 13917544438 202 in 2006-04-08 11:57:15.000
统计效果如下
时间段 agentid 第1天记录数量(in) 第2天记录数量(in) 第1天记录数量(out) 第2天记录数量(out)
08:00-08:59 201 2 0 1 0
08:00-08:59 202 0 0 0 0
08:00-08:59小计 2 0 1 009:00-09:59 201 0 1 1 2
09:00-09:59 202 1 2 1 0
09:00-09:59小计 1 3 2 211:00-11:59 201 0 0 0 0
11:00-11:59 202 0 1 0 0
11:00-11:59小计 0 1 0 0合计 3 4 2 3求实现方式SQL 如果一句SQL搞不定,可以分开实现
----------- ------------ ---- ---- -------------------------------
31387 16062567655 201 in 2006-03-05 08:19:02.000
31388 50380088 201 in 2006-03-05 08:23:30.000
31389 13916089473 202 out 2006-03-05 08:25:53.00031390 13524615926 202 out 2006-03-05 09:27:23.000
31423 13381754439 201 out 2006-03-05 09:38:43.000
31424 65039950 202 in 2006-03-05 09:40:16.000
31504 64870891 201 out 2006-04-08 09:09:22.000
31505 64870871 201 in 2006-04-08 09:17:13.000
31506 64870891 202 in 2006-04-08 09:36:07.000
31507 64870851 201 out 2006-04-08 09:44:56.000
31508 64870841 202 in 2006-04-08 09:55:46.00031442 13917544438 202 in 2006-04-08 11:57:15.000
统计效果如下
时间段 agentid 第1天记录数量(in) 第2天记录数量(in) 第1天记录数量(out) 第2天记录数量(out)
08:00-08:59 201 2 0 1 0
08:00-08:59 202 0 0 0 0
08:00-08:59小计 2 0 1 009:00-09:59 201 0 1 1 2
09:00-09:59 202 1 2 1 0
09:00-09:59小计 1 3 2 211:00-11:59 201 0 0 0 0
11:00-11:59 202 0 1 0 0
11:00-11:59小计 0 1 0 0合计 3 4 2 3求实现方式SQL 如果一句SQL搞不定,可以分开实现
declare @t table (
id int,
ani varchar(20),
agentID varchar(20),
type varchar(20),
[datetime] datetime
)
insert @t
select
31387, '16062567655', '201', 'in', '2006-03-05 08:19:02'
union all select
31388, '50380088' , '201', 'in' , '2006-03-05 08:23:30'
union all select
31389, '13916089473' , '202', 'out' , '2006-03-05 08:25:53'
union all select
31390, '13524615926' , '202', 'out' , '2006-03-05 09:27:23'
union all select
31423, '13381754439' , '201', 'out' ,'2006-03-05 09:38:43'
union all select
31424, '65039950' ,'202', 'in', '2006-03-05 09:40:16'
union all select
31504, '64870891', '201', 'out', '2006-04-08 09:09:22'
union all select
31505, '64870871' , '201', 'in' , '2006-04-08 09:17:13'
union all select
31506, '64870891' , '202', 'in' , '2006-04-08 09:36:07'
union all select
31507, '64870851' , '201', 'out' ,'2006-04-08 09:44:56'
union all select
31508, '64870841' , '202', 'in', '2006-04-08 09:55:46'
union all select
31442, '13917544438' , '202', 'in' , '2006-04-08 11:57:15'select case when grouping(时间段)=1 then '合计' else 时间段 end as 时间段,
case when grouping(agentid)=1 then '小计' else agentid end as agentid,
sum(case when dt='2006-03-05' and type='in' then 1 else 0 end) as [第1天记录数量(in)],
sum(case when dt='2006-04-08' and type='in' then 1 else 0 end) as [第2天记录数量(in)],
sum(case when dt='2006-03-05' and type='out' then 1 else 0 end) as [第1天记录数量(out)],
sum(case when dt='2006-04-08' and type='out' then 1 else 0 end) as [第2天记录数量(out)]
from (
select case when convert(varchar(5),[datetime],108) between '08:00' and '08:59' then '08:00-08:59'
when convert(varchar(5),[datetime],108) between '09:00' and '09:59' then '09:00-09:59'
when convert(varchar(5),[datetime],108) between '10:00' and '10:59' then '10:00-10:59'
when convert(varchar(5),[datetime],108) between '11:00' and '11:59' then '11:00-11:59'
when convert(varchar(5),[datetime],108) between '12:00' and '12:59' then '12:00-12:59'
when convert(varchar(5),[datetime],108) between '13:00' and '13:59' then '13:00-13:59'
when convert(varchar(5),[datetime],108) between '14:00' and '14:59' then '14:00-14:59'
when convert(varchar(5),[datetime],108) between '15:00' and '15:59' then '15:00-15:59'
when convert(varchar(5),[datetime],108) between '16:00' and '16:59' then '16:00-16:59'
when convert(varchar(5),[datetime],108) between '17:00' and '17:59' then '17:00-17:59'
when convert(varchar(5),[datetime],108) between '18:00' and '18:59' then '18:00-18:59'
when convert(varchar(5),[datetime],108) between '19:00' and '19:59' then '19:00-19:59'
when convert(varchar(5),[datetime],108) between '20:00' and '20:59' then '20:00-20:59'
when convert(varchar(5),[datetime],108) between '21:00' and '21:59' then '21:00-21:59'
when convert(varchar(5),[datetime],108) between '22:00' and '22:59' then '22:00-22:59'
when convert(varchar(5),[datetime],108) between '23:00' and '23:59' then '23:00-23:59'
end as 时间段,agentid,type,convert(varchar(10),[datetime],120) as dt
from @t
) as t
group by 时间段,agentid
with ROLLUP--结果不贴了,和你的一样
-->
case when grouping(agentid)=1 and grouping(时间段)=1 then '' when grouping(时间段)=1 then '合计' else 时间段 end as 时间段,
FROM(SELECT DISTINCT (CONVERT(CHAR(2),(A.datetime),114)) AS time_tmp FROM 表) A
full join (SELECT DISTINCT agentid AS agentid_tmp FROM 表) b on 1=1LEFT JOIN
(SELECT CONVERT(CHAR(2),(A.datetime),114) AS TIME1,agentid AS agentid1_IN,count(1) AS count1_IN
FROM 表 WHERE CONVERT(CHAR(10),datetime,21)='2006-03-05' AND TYPE='in') C ON A.time_tmp=C.TIME1 AND B.agentid=C.agentid1_INLEFT JOIN
(SELECT CONVERT(CHAR(2),(A.datetime),114) AS TIME2,agentid AS agentid2_IN,count(1) AS count2_IN
FROM 表 WHERE CONVERT(CHAR(10),datetime,21)='2006-04-08' AND TYPE='in') D ON A.time_tmp=D.TIME2 AND B.agentid=D.agentid2_IN
LEFT JOIN
(SELECT CONVERT(CHAR(2),(A.datetime),114) AS TIME1,agentid AS agentid1_OUT,count(1) AS count1_OUT
FROM 表 WHERE CONVERT(CHAR(10),datetime,21)='2006-03-05' AND TYPE='out') E ON A.time_tmp=E.TIME1 AND B.agentid=E.agentid1_INLEFT JOIN
(SELECT CONVERT(CHAR(2),(A.datetime),114) AS TIME2,agentid AS agentid2_OUT,count(1) AS count2_OUT
FROM 表 WHERE CONVERT(CHAR(10),datetime,21)='2006-04-08' AND TYPE='out') F ON A.time_tmp=F.TIME2 AND B.agentid=F.agentid2_IN加统计,可以在一个sql语句中用union all 实现,分开来
如果搂主要求
11:00-11:59 201 0 0 0 0
11:00-11:59 202 0 1 0 0
全0的记录(201)也要出来,好像你的解法就不好实现
insert into l_test select
31387, '16062567655', '201', 'in', '2006-03-05 08:19:02.000' union all select
31388, '50380088', '201', 'in', '2006-03-05 08:23:30.000' union all select
31389, '13916089473', '202', 'out', '2006-03-05 08:25:53.000' union all select
31390, '13524615926', '202', 'out', '2006-03-05 09:27:23.000' union all select
31423, '13381754439', '201', 'out', '2006-03-05 09:38:43.000' union all select
31424, '65039950', '202', 'in', '2006-03-05 09:40:16.000' union all select
31504, '64870891', '201', 'out', '2006-04-08 09:09:22.000' union all select
31505, '64870871', '201', 'in', '2006-04-08 09:17:13.000' union all select
31506, '64870891', '202', 'in', '2006-04-08 09:36:07.000' union all select
31507, '64870851', '201', 'out', '2006-04-08 09:44:56.000' union all select
31508, '64870841', '202', 'in', '2006-04-08 09:55:46.000' union all select
31442, '13917544438', '202', 'in', '2006-04-08 11:57:15.000'
godeclare @date1 varchar(10),@date2 varchar(10)
set @date1='2006-03-05'
set @date2='2006-04-08'
select
case when grouping(时间段)=1 then '合计' else 时间段 end 时间段,
case when grouping(时间段)=1 and grouping(agentid)=1 then '' when grouping(agentid)=1 then '小计' else agentid end agentid,
sum(case when type='in' and 时间=@date1 then 数量 else 0 end) [第1天记录数量(in)],
sum(case when type='in' and 时间=@date2 then 数量 else 0 end) [第2天记录数量(in)],
sum(case when type='out' and 时间=@date1 then 数量 else 0 end) [第1天记录数量(out)],
sum(case when type='out' and 时间=@date2 then 数量 else 0 end) [第2天记录数量(out)]
from
(select
convert(varchar(10),datetime,120) 时间,
left(convert(varchar,datetime,114),2)+':00-'+left(convert(varchar,datetime,114),2)+':59' 时间段,
agentid,type,count(1) 数量 from l_test
group by convert(varchar(10),datetime,120),
left(convert(varchar,datetime,114),2)+':00-'+left(convert(varchar,datetime,114),2)+':59',
agentid,type ) A
group by 时间段,agentid
with rollup
时间段 agentid 第1天记录数量(in) 第2天记录数量(in) 第1天记录数量(out) 第2天记录数量(out)
--------------- -------- ----------- ----------- ------------ ------------
08:00-08:59 201 2 0 0 0
08:00-08:59 202 0 0 1 0
08:00-08:59 小计 2 0 1 0
09:00-09:59 201 0 1 1 2
09:00-09:59 202 1 2 1 0
09:00-09:59 小计 1 3 2 2
11:00-11:59 202 0 1 0 0
11:00-11:59 小计 0 1 0 0
合计 3 4 3 2(所影响的行数为 9 行)
id int,
ani varchar(20),
agentid varchar(20),
type varchar(20),
[datetime] datetime
)
insert @t
select
31387, '16062567655', '201', 'in', '2006-03-05 08:19:02'
union all select
31388, '50380088' , '201', 'in' , '2006-03-05 08:23:30'
union all select
31389, '13916089473' , '202', 'out' , '2006-03-05 08:25:53'
union all select
31390, '13524615926' , '202', 'out' , '2006-03-05 09:27:23'
union all select
31423, '13381754439' , '201', 'out' ,'2006-03-05 09:38:43'
union all select
31424, '65039950' ,'202', 'in', '2006-03-05 09:40:16'
union all select
31504, '64870891', '201', 'out', '2006-04-08 09:09:22'
union all select
31505, '64870871' , '201', 'in' , '2006-04-08 09:17:13'
union all select
31506, '64870891' , '202', 'in' , '2006-04-08 09:36:07'
union all select
31507, '64870851' , '201', 'out' ,'2006-04-08 09:44:56'
union all select
31508, '64870841' , '202', 'in', '2006-04-08 09:55:46'
union all select
31442, '13917544438' , '202', 'in' , '2006-04-08 11:57:15'
SELECT (case when grouping(A.time_tmp)=1 then '合计' else A.time_tmp+':00-'+ A.time_tmp+ ':59' end ) AS '时间段',
(case when grouping(B.agentid)=1 then '小计' else B.agentid end) as agentid,
sum(isnull(count1_IN,0)) AS '第1天记录数量(in)',sum(isnull(count2_IN,0)) AS '第2天记录数量(in)',
sum(isnull(count1_OUT,0)) AS '第1天记录数量(OUT)',sum(isnull(count2_OUT,0)) AS '第2天记录数量(OUT)'
FROM
(SELECT DISTINCT (CONVERT(CHAR(2),[datetime],114)) AS time_tmp FROM @t) A
full join (SELECT DISTINCT agentid AS agentid FROM @t) B on 1=1
LEFT JOIN
(SELECT CONVERT(CHAR(2),[datetime],114) time_TMP,agentid,
sum(case when CONVERT(CHAR(10),[datetime],21)='2006-03-05' AND type='in' THEN 1 ELSE 0 END) count1_IN,
sum(case when CONVERT(CHAR(10),[datetime],21)='2006-04-08' AND type='in' THEN 1 ELSE 0 END) count2_IN,
sum(case when CONVERT(CHAR(10),[datetime],21)='2006-03-05' AND type='out' THEN 1 ELSE 0 END) count1_OUT,
sum(case when CONVERT(CHAR(10),[datetime],21)='2006-04-08' AND type='out' THEN 1 ELSE 0 END) count2_OUT
FROM @t group by CONVERT(CHAR(2),[datetime],114),agentid) C ON A.time_tmp=C.time_TMP AND B.agentid=C.agentid
group by A.time_tmp,B.agentid WITH ROLLUP
现学现用~——获益匪浅,在这里谢过 zicxc(冒牌邹建 V0.3)~