按两小时分组

[liuconjun666]

我要做一个报表，数据是5分钟记录一次，现在要按2个小时分组统计这两个小时的平均值，格式如下：

时间 压差 压力 温度 流量
08:00-10:00 18975.8 0.17 43.7 2528.9
10:00-12:00 18975.8 0.17 43.7 2528.9
12:00-14:00 18975.8 0.17 43.7 2528.9
14:00-16:00 18975.8 0.17 43.7 2528.9
16:00-18:00 18975.8 0.17 43.7 2528.9
18:00-20:00 18975.8 0.17 43.7 2528.9
20:00-22:00 18975.8 0.17 43.7 2528.9
22:00-00:00 18975.8 0.17 43.7 2528.9
00:00-02:00 18975.8 0.17 43.7 2528.9
02:00-04：00 18975.8 0.17 43.7 2528.9
04:00-06:00 18975.8 0.17 43.7 2528.9
06:00-08:00 18975.8 0.17 43.7 2528.9
我现在的语句如下：
select convert(char(2),[riqi],108),avg(yc),avg(yl),avg(wd), avg(ll) from ribao where riqi between '2012-10-28 08:00:00' and'2012-10-29 08:00:00' group by convert(char(2),[riqi],108) order by 1
但这个语句只能按每个小时分组，希望得到高手的指教，谢谢。测试数据如下：

2012-10-28 10:34:39 0 0 0 0
2012-10-28 10:38:05 0 0 0 0
2012-10-28 10:47:07 0 0 0 0
2012-10-28 10:52:07 60 60 60 60
2012-10-28 10:57:07 146 147 147 147
2012-10-28 11:13:01 62 62 62 62
2012-10-28 11:13:01 62 62 62 62
2012-10-28 11:13:01 62 62 62 62
2012-10-28 11:17:07 93 93 93 93
2012-10-28 11:22:07 180 180 180 180
2012-10-28 11:27:07 67 67 67 67
2012-10-28 11:32:07 155 155 155 155
2012-10-28 11:37:07 43 43 43 43
2012-10-28 11:42:07 131 131 131 131
2012-10-28 11:47:07 17 17 17 17
2012-10-28 11:52:07 104 104 104 104
2012-10-28 11:57:07 191 191 191 191
2012-10-28 12:02:07 78 78 78 78
2012-10-28 12:07:07 165 165 165 165
2012-10-28 12:12:07 52 52 52 52
2012-10-28 12:17:07 139 139 139 139
2012-10-28 12:22:07 26 26 26 26
2012-10-28 12:27:07 113 113 113 113
2012-10-28 12:32:07 200 200 200 200
2012-10-28 12:37:07 86 86 86 86
2012-10-28 12:42:07 169 169 169 169
2012-10-28 12:47:07 56 56 56 56
2012-10-28 12:52:07 145 145 145 145
2012-10-28 12:57:07 32 32 32 32
2012-10-28 13:02:07 120 120 120 120
2012-10-28 13:07:07 6 6 6 6
2012-10-28 13:12:07 94 94 94 94
2012-10-28 13:17:07 182 182 182 182
2012-10-28 13:22:07 69 69 69 69
2012-10-28 13:27:07 157 157 157 157
2012-10-28 18:22:05 0 0 0 0
2012-10-28 18:27:05 0 0 0 0
2012-10-28 18:32:05 0 0 0 0
2012-10-28 18:37:05 0 0 0 0
2012-10-28 18:42:05 0 0 0 0

[shoppo0505]

把小时数除以2，做为int值存入附加列，然后根据附加列的值进行group by

[汤姆克鲁斯]

[Quote=引用 1 楼 的回复:]

把小时数除以2，做为int值存入附加列，然后根据附加列的值进行group by
[/Quote]
+1
小时/2

[弘恩]

SELECT CONVERT(CHAR(11),GETDATE(),120 ) + RIGHT( '0'+CAST( FLOOR( DATEPART(HOUR,[riqi] ) /2 ) *2 AS VARCHAR(2)),2) ,
AVG(yc) ,
AVG(yl) ,
AVG(wd) ,
AVG(ll)
FROM ribao
WHERE riqi BETWEEN '2012-10-28 08:00:00' AND '2012-10-29 08:00:00'
GROUP BY CONVERT(CHAR(11),GETDATE(),120 ) + RIGHT( '0'+CAST( FLOOR( DATEPART(HOUR,[riqi] ) /2 ) *2 AS VARCHAR(2)),2)
ORDER BY 1