34,594
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #T
(a int,b datetime)
INSERT #T
SELECT 1,convert(datetime,'2009-3-20 8:00:00',120)
UNION ALL
SELECT 3,convert(datetime,'2009-3-20 8:05:00',120)
UNION ALL
SELECT 4,convert(datetime,'2009-3-20 8:10:00',120)
UNION ALL
SELECT 5,convert(datetime,'2009-3-20 8:15:00',120)
UNION ALL
SELECT 7,convert(datetime,'2009-3-20 8:20:00',120)
UNION ALL
SELECT 8,convert(datetime,'2009-3-20 8:25:00',120)
UNION ALL
SELECT 9,convert(datetime,'2009-3-20 8:30:00',120)
UNION ALL
SELECT 10,convert(datetime,'2009-3-20 8:35:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:40:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:45:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:50:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:55:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 9:00:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 9:05:00',120)
CREATE TABLE #T
(a int,b datetime)
INSERT #T
SELECT 1,convert(datetime,'2009-3-20 8:00:00',120)
UNION ALL
SELECT 3,convert(datetime,'2009-3-20 8:05:00',120)
UNION ALL
SELECT 4,convert(datetime,'2009-3-20 8:10:00',120)
UNION ALL
SELECT 5,convert(datetime,'2009-3-20 8:15:00',120)
UNION ALL
SELECT 7,convert(datetime,'2009-3-20 8:20:00',120)
UNION ALL
SELECT 8,convert(datetime,'2009-3-20 8:25:00',120)
UNION ALL
SELECT 9,convert(datetime,'2009-3-20 8:30:00',120)
UNION ALL
SELECT 10,convert(datetime,'2009-3-20 8:35:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:40:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:45:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:50:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:55:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 9:00:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 9:05:00',120)
--声明GRANULARITY变量
DECLARE @GRANULARITY INT
SET @GRANULARITY=900
SELECT
DATEADD(minute,DATEDIFF(minute,0,b)/(@GRANULARITY/60)*(@GRANULARITY/60) ,0) AS b,
COUNT(*) AS cnt,
SUM(a) AS a
FROM
#T
GROUP BY
DATEADD(minute,DATEDIFF(minute,0,b)/(@GRANULARITY/60)*(@GRANULARITY/60) ,0)
DROP TABLE #T
/*
b cnt a
----------------------- ----------- -----------
2009-03-20 08:00:00.000 3 8
2009-03-20 08:15:00.000 3 20
2009-03-20 08:30:00.000 3 20
2009-03-20 08:45:00.000 3 3
2009-03-20 09:00:00.000 2 2
(5 行受影响)
*/
CREATE TABLE #T
(a int,b datetime)
INSERT #T
SELECT 1,convert(datetime,'2009-3-20 8:00:00',120)
UNION ALL
SELECT 3,convert(datetime,'2009-3-20 8:05:00',120)
UNION ALL
SELECT 4,convert(datetime,'2009-3-20 8:10:00',120)
UNION ALL
SELECT 5,convert(datetime,'2009-3-20 8:15:00',120)
UNION ALL
SELECT 7,convert(datetime,'2009-3-20 8:20:00',120)
UNION ALL
SELECT 8,convert(datetime,'2009-3-20 8:25:00',120)
UNION ALL
SELECT 9,convert(datetime,'2009-3-20 8:30:00',120)
UNION ALL
SELECT 10,convert(datetime,'2009-3-20 8:35:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:40:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:45:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:50:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 8:55:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 9:00:00',120)
UNION ALL
SELECT 1,convert(datetime,'2009-3-20 9:05:00',120)
--声明GRANULARITY变量
DECLARE @GRANULARITY INT
SET @GRANULARITY=900
SELECT DATEADD(minute,DATEDIFF(minute,0,b)/(@GRANULARITY/60)*(@GRANULARITY/60) ,0) AS b,COUNT(*)
FROM #T
GROUP BY DATEADD(minute,DATEDIFF(minute,0,b)/(@GRANULARITY/60)*(@GRANULARITY/60) ,0)
DROP TABLE #T
/*
b
----------------------- -----------
2009-03-20 08:00:00.000 3
2009-03-20 08:15:00.000 3
2009-03-20 08:30:00.000 3
2009-03-20 08:45:00.000 3
2009-03-20 09:00:00.000 2
(5 行受影响)
*/