create table tb (id int,score int,inputDate datetime)
insert into tb values(1, 2, '2007-4-5 3:33:33')
insert into tb values(2, 1, '2007-4-5 4:33:33')
insert into tb values(3, 4, '2007-3-5 3:33:33')
insert into tb values(4, 2, '2007-4-2 2:33:33')
go
select top 24 id=identity( int,0,1) into tmp from syscolumns a,syscolumns bselect convert(varchar(5),dateadd(hour,b.id*1,convert(varchar(10),inputdate,120)),114) + '-' +
convert(varchar(5),dateadd(hour,(b.id+1)*1,convert(varchar(10),inputdate,120)),114) 时间段,sum(score) score
from tb , tmp b
where convert(varchar(5),inputdate,114) >= convert(varchar(5),dateadd(hour,b.id*1,convert(varchar(10),inputdate,120)),114)
and convert(varchar(5),inputdate,114) < convert(varchar(5),dateadd(hour,(b.id+1)*1,convert(varchar(10),inputdate,120)),114)
group by convert(varchar(5),dateadd(hour,b.id*1,convert(varchar(10),inputdate,120)),114) + '-' +
convert(varchar(5),dateadd(hour,(b.id+1)*1,convert(varchar(10),inputdate,120)),114)drop table tb,tmp/*
时间段 score
----------- -----------
02:00-03:00 2
03:00-04:00 6
04:00-05:00 1
(所影响的行数为 3 行)
*/
insert into tb values(1, 2, '2007-4-5 3:33:33')
insert into tb values(2, 1, '2007-4-5 4:33:33')
insert into tb values(3, 4, '2007-3-5 3:33:33')
insert into tb values(4, 2, '2007-4-2 2:33:33')
go
select top 24 id=identity( int,0,1) into tmp from syscolumns a,syscolumns bselect convert(varchar(5),dateadd(hour,b.id*1,convert(varchar(10),inputdate,120)),114) + '-' +
convert(varchar(5),dateadd(hour,(b.id+1)*1,convert(varchar(10),inputdate,120)),114) 时间段,sum(score) score
from tb , tmp b
where convert(varchar(5),inputdate,114) >= convert(varchar(5),dateadd(hour,b.id*1,convert(varchar(10),inputdate,120)),114)
and convert(varchar(5),inputdate,114) < convert(varchar(5),dateadd(hour,(b.id+1)*1,convert(varchar(10),inputdate,120)),114)
group by convert(varchar(5),dateadd(hour,b.id*1,convert(varchar(10),inputdate,120)),114) + '-' +
convert(varchar(5),dateadd(hour,(b.id+1)*1,convert(varchar(10),inputdate,120)),114)drop table tb,tmp/*
时间段 score
----------- -----------
02:00-03:00 2
03:00-04:00 6
04:00-05:00 1
(所影响的行数为 3 行)
*/
insert @a select 1, 2, '2007-4-5 3:33:33'
union all select 2, 1, '2007-4-5 4:33:33'
union all select 3, 4, '2007-3-5 3:33:33'
union all select 4, 2, '2007-4-2 2:33:33'select right(100+a,2)+':00-'+right(100+b,2)+':00',
sum(case when datepart(hour,inputdate) >=a and datepart(hour,inputdate)<b then score else 0 end)
from @a a ,
(select 0 a,1 b
union all select 1,2
union all select 2,3
union all select 3,4
union all select 4,5
union all select 5,6
union all select 6,7
union all select 7,8
union all select 8,9
union all select 9,10
union all select 10,11
union all select 11,12
union all select 12,13
union all select 13,14
union all select 14,15
union all select 15,16
union all select 16,17
union all select 17,18
union all select 18,19
union all select 19,20
union all select 20,21
union all select 21,22
union all select 22,23
union all select 23,24
)aa
group by right(100+a,2)+':00-'+right(100+b,2)+':00'
/*
--------------- -----------
00:00-01:00 0
01:00-02:00 0
02:00-03:00 2
03:00-04:00 6
04:00-05:00 1
05:00-06:00 0
06:00-07:00 0
07:00-08:00 0
08:00-09:00 0
09:00-10:00 0
10:00-11:00 0
11:00-12:00 0
12:00-13:00 0
13:00-14:00 0
14:00-15:00 0
15:00-16:00 0
16:00-17:00 0
17:00-18:00 0
18:00-19:00 0
19:00-20:00 0
20:00-21:00 0
21:00-22:00 0
22:00-23:00 0
23:00-24:00 0(所影响的行数为 24 行)
*/
至少ORACLE的时间算法和SQL SERVER的不一样.
insert @a select 1, 2, '2011-11-1 3:33:33 '
union all select 2, 1, '2011-11-2 4:33:33 '
union all select 3, 4, '2011-11-3 3:33:33 '
union all select 4, 2, '2011-11-4 2:33:33 ' select 时间段=right(100+number,2)+':00-'+right(101+number,2)+':00',
统计=sum(case when datepart(hour,inputDate)>=number and datepart(hour,inputdate)<number+1 then score else 0 end)
from master..spt_values ,@a
where type='p'and number between 0 and 23
group by right(100+number,2)+':00-'+right(101+number,2)+':00'
/*
时间段 统计
--------------- -----------
00:00-01:00 0
01:00-02:00 0
02:00-03:00 2
03:00-04:00 6
04:00-05:00 1
05:00-06:00 0
06:00-07:00 0
07:00-08:00 0
08:00-09:00 0
09:00-10:00 0
10:00-11:00 0
11:00-12:00 0
12:00-13:00 0
13:00-14:00 0
14:00-15:00 0
15:00-16:00 0
16:00-17:00 0
17:00-18:00 0
18:00-19:00 0
19:00-20:00 0
20:00-21:00 0
21:00-22:00 0
22:00-23:00 0
23:00-24:00 0(24 行受影响)
*/