请教：按小时分组 统计其它字段的SQL语句怎么写
比如有一个表timeTable,有字段id,score,inputDate.
数据如下
id score inputDate
1 2 '2007-4-5 3:33:33 '
2 1 '2007-4-5 4:33:33 '
3 4 '2007-3-5 3:33:33 '
4 2 '2007-4-2 2:33:33 '
我要按时间分组统计score，结果如下
0:00-1:00 0
1:00-2:00 0
2:00-3:00 2
3:00-4:00 6
4:00-5:00 1
5:00-6:00 0
....
23:00-24:00 0
[解决办法]
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 b
select 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 行）
*/
[解决办法]
declare @a table(id int, score int, inputDate smalldatetime)
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 '
[解决办法]
--result
/*
--------------- -----------
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的不一样.
