如何按小时分组统计
有一表
id 起始时间 截止时间 字段 A
1 2007-05-06 02:00:00 2007-05-07 04:03:00 XXX
2 2007-05-06 11:00:00 2007-05-06 12:30:00 XXX
3 2007-05-06 12:00:00 2007-05-06 12:30:00 XXX
3 2007-05-07 12:00:00 2007-05-10 12:30:00 XXX
现在想分每天按小时 达到如下统计效果 需要根据字段A分组
2007-05-06的情况
小时段 count
0：00-1:00 0
1：00-2:00 1
2：00-3:00 1
3：00-4:00 1
4：00-5:00 1
5：00-6:00 1
......
11：00-12:00 2
12:00-13:00 3
......
22：00-23:00 1
23:00-24:00 1
2007-05-07的情况
小时段 count
0：00-1:00 0
1：00-2:00 0
2：00-3:00 1
3：00-4:00 1
4：00-5:00 1
5：00-6:00 0
......
11：00-12:00 0
12:00-13:00 1
......
22：00-23:00 1
23:00-24:00 1
[解决办法]
建个辅助表。
create table tb (start_hour int,end_hour varchar(10),sec varchar(20))
insert into tb
select 0,0:59:59, '0：00-1:00 ' union all
select 1,1:59:59, '1：00-1:00 ' union all
....
select 23,23:59:59, '23：00-24:00 '
declare @s varchar(10)--输入查询日期
set @s= '2006-05-07 '
select count(a.*) as count,b.sec as 小时段 from table a inner join tb b on cast(@s+ ' '+b.end_hour as datetime) between and a.起始时间 and 截止时间
[解决办法]
declare @t table (
id int,
起始时间 datetime,
截止时间 datetime,
字段A varchar(5)
)
insert @t select
1, '2007-05-06 02:00:00 ', '2007-05-07 04:03:00 ', 'XXX '
union all select
2, '2007-05-06 11:00:00 ', '2007-05-06 12:30:00 ', 'XXX '
union all select
3, '2007-05-06 12:00:00 ', '2007-05-06 12:30:00 ', 'XXX '
union all select
3, '2007-05-07 12:00:00 ', '2007-05-10 12:30:00 ', 'XXX '
declare @dt datetime
set @dt= '2007-5-6 ' --查询参数
select
cast(小时段 as varchar)+ ':00- '+cast(小时段+1 as varchar)+ ':00 ' as 小时段,
sum(case when dateadd(hour,小时段,@dt) between 起始时间 and 截止时间 or dateadd(hour,小时段+1,@dt) between 起始时间 and 截止时间 then 1 else 0 end) as [count]
from @t a,(
select 0 as 小时段
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 15
union all select 16
union all select 17
union all select 18
union all select 19
union all select 20
union all select 21
union all select 22
union all select 23
) as t
group by 小时段
--结果
小时段 count
------------------------------- -----------
0:00-1:00 0
1:00-2:00 1
2:00-3:00 1
3:00-4:00 1
4:00-5:00 1
5:00-6:00 1
6:00-7:00 1
7:00-8:00 1
8:00-9:00 1
9:00-10:00 1
10:00-11:00 2
11:00-12:00 3
12:00-13:00 3
13:00-14:00 1
14:00-15:00 1
15:00-16:00 1
16:00-17:00 1
17:00-18:00 1
18:00-19:00 1
19:00-20:00 1
20:00-21:00 1
21:00-22:00 1
22:00-23:00 1
23:00-24:00 1
（所影响的行数为 24 行）
[解决办法]
create proc proc_xyz
@sj smalldatetime
as
set nocount on
declare @a table(id int, 起始时间 smalldatetime, 截止时间 smalldatetime, A varchar(10))
insert @a select 1 , '2007-05-06 02:00:00 ', '2007-05-07 04:03:00 ', 'aaa '
insert @a select 2 , '2007-05-06 11:00:00 ', '2007-05-06 12:30:00 ', 'bbb '
insert @a select 3 , '2007-05-06 12:00:00 ', '2007-05-06 12:30:00 ', 'aaa '
insert @a select 3 , '2007-05-07 12:00:00 ', '2007-05-10 12:30:00 ', 'ccc '
declare @b table(id int,qs smalldatetime,jz smalldatetime,sj smalldatetime,a varchar(10))
select top 100 id=identity(int,0,1) into # from syscolumns
select top 24 id=identity(int,0,1) into #t from syscolumns
insert @b select a.id,a.起始时间,a.截止时间, dateadd(hour,b.id,起始时间),a from @a a ,# b where dateadd(hour,b.id,起始时间) <=截止时间
select isnull(sj,convert(varchar(10),@sj,120)) 时间,
ltrim(id)+ ':00- '+ltrim(id+1)+ ':00 ' 小时段,isnull(a, ' ') 值,isnull(min(num),0) 数量 from
#t aa
left join
(
select a,convert(varchar(10),sj,120) sj,datepart(hour,sj) xs,count(1) num
from @b b where datediff(day,@sj,sj)=0
group by convert(varchar(10),sj,120),datepart(hour,sj),a
)bb
on aa.id=bb.xs
group by sj,id,a
order by 1,id
drop table #,#t
go
proc_xyz '2007-05-07 '
[解决办法]
select a.小时段,count(b.id) as sl from (
select 0 as hh, '00:00-01:00 ' as 小时段
union
select 1, '01:00-02:00 '
union
select 2, '02:00-03:00 '
union
select 3, '03:00-04:00 '
union
select 4, '04:00-05:00 '
union
select 5, '05:00-06:00 '
union
select 6, '06:00-07:00 '
union
select 7, '07:00-08:00 '
union
select 8, '08:00-09:00 '
union
select 9, '09:00-10:00 '
union
select 10, '10:00-11:00 '
union
select 11, '11:00-12:00 '
union
select 12, '12:00-13:00 '
union
select 13, '13:00-14:00 '
union
select 14, '14:00-15:00 '
union
select 15, '15:00-16:00 '
union
select 16, '16:00-17:00 '
union
select 17, '17:00-18:00 '
union
select 18, '18:00-19:00 '
union
select 19, '19:00-20:00 '
union
select 20, '20:00-21:00 '
union
select 21, '21:00-22:00 '
union
select 22, '22:00-23:00 '
union
select 23, '23:00-24:00 '
)
