--这个按每个小时的. 比如有一个表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 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' --------------- ----------- 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 行）
代码
