62,046
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('t1','U') IS NOT NULL
DROP TABLE t1
GO
CREATE TABLE t1(
time1 DATETIME,
data INT
)
GO
INSERT t1(time1, data)
VALUES('2013-11-12 21:22:23',33),
('2013-11-12 21:33:23',234),
('2013-11-12 22:45:56',5),
('2013-11-12 23:45:56',35234),
('2013-11-12 00:45:56',35),
('2013-11-12 01:45:56',343)
GO
解决你的问题
WITH t2(time2,data2)
AS
(
SELECT DATEPART(hh,time1),data FROM t1 WHERE time1>'2013-1-1' AND time1<'2013-12-31'
)
SELECT time2,SUM(data2) FROM t2 GROUP BY time2
DECLARE @t TABLE(时间 DATETIME NOT NULL, 数据 INT NOT NULL)
INSERT @t(时间, 数据)
VALUES('2013-11-12 21:22:23',33),
('2013-11-12 21:33:23',234),
('2013-11-12 22:45:56',5),
('2013-11-12 23:45:56',35234),
('2013-11-12 00:45:56',35),
('2013-11-12 01:45:56',343);
WITH t AS (SELECT CONVERT(varchar(13),时间,120) 时间,数据 FROM @t)
SELECT 时间,SUM(t.数据) 数据 FROM t GROUP BY t.时间