2015-09-01 00：00：00.000 A 1
2015-09-01 01：00：00.000 A 1
2015-09-01 02：00：00.000 A 1
2015-09-02 03：00：00.000 B 2
2015-09-01 04：00：00.000 B 2
2015-09-01 05：00：00.000 B 2
2015-09-01 06：00：00.000 A 1
2015-09-01 07：00：00.000 A 1
2015-09-01 08：00：00.000 A 1
2015-09-01 09：00：00.000 B 1
2015-09-01 10：00：00.000 B 1
2015-09-01 11：00：00.000 B 1
2015-09-01 00:00:00.000 A 3
2015-09-01 03:00:00.000 B 6
2015-09-01 06:00:00.000 A 3
2015-09-01 09:00:00.000 B 3
我的表是上面第一个，是每三个小时A和B会交换一下，我想要的结果是，查询一个月的数据。每三个小时合并成一条，最后面的数字是SUM，就是下面的表，查询条件是按月查询。显示这一个月这样的数据
------解决思路----------------------
with tb (sdate,name,id)
as
(
select '2015-09-01 00:00:00.000','A', 1 union all
select '2015-09-01 01:00:00.000','A', 1 union all
select '2015-09-01 02:00:00.000','A', 1 union all
select '2015-09-01 03:00:00.000','B', 2 union all
select '2015-09-01 04:00:00.000','B', 2 union all
select '2015-09-01 05:00:00.000','B', 2
)
select min(sdate)sdate,name,SUM(id)id from tb group by DATEPART(HH,sdate)/3,name
------解决思路----------------------
还有你第四条数据错了吗 是2015-09-01吗
------解决思路----------------------
#1会把多天的3小时统计在一起
/* 测试数据
WITH table1(dt,code,value)AS(
SELECT '2015-09-01 00:00:00.000','A',1 UNION ALL
SELECT '2015-09-01 01:00:00.000','A',1 UNION ALL
SELECT '2015-09-01 02:00:00.000','A',1 UNION ALL
SELECT '2015-09-01 03:00:00.000','B',2 UNION ALL
SELECT '2015-09-01 04:00:00.000','B',2 UNION ALL
SELECT '2015-09-01 05:00:00.000','B',2 UNION ALL
SELECT '2015-09-02 00:00:00.000','A',1 UNION ALL
SELECT '2015-09-02 01:00:00.000','A',1 UNION ALL
SELECT '2015-09-02 02:00:00.000','A',1 UNION ALL
SELECT '2015-09-02 03:00:00.000','B',3 UNION ALL
SELECT '2015-09-02 04:00:00.000','B',2 UNION ALL
SELECT '2015-09-02 05:00:00.000','B',3
)*/
SELECT MIN(dt) dt,
MIN(code) code,
SUM(value) value
FROM table1
GROUP BY DATEDIFF(hour,'2015-09-01',dt)/3
dt code value
----------------------- ---- -----------
2015-09-01 00:00:00.000 A 3
2015-09-01 03:00:00.000 B 6
2015-09-02 00:00:00.000 A 3
2015-09-02 03:00:00.000 B 8