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,是以秒钟来存放的
例如存放为300,代表5分钟,如果为900,代表15分钟,粒度的值通过程序进行设定,在这里假设是一个常量现在的需求是:
当粒度为5分种的时候,所有数据按照5分钟进行汇总,查出的结果和上面一样
当为15分钟的时候,结果是把8:00 8:05 8:10汇总为1条 8:15 8:20 8:25汇总为1条 以次类推,最后9:00那里只有2条,也汇总为1条
当为30分钟的时候,把6条汇总为1条如果其中某条数据没有
例如8:00的数据没有,按照15 分钟的时候,就只把8:05 8:10的两条进行汇总
例如8:00的数据没有,按照30 分钟的时候,就只把8:05 10 15 20 25的五条进行汇总这里的意思就是说,如果粒度为15分钟,一个小时就必须汇总为4段,如果某段数据不完整,也需要进行汇总看大家对我的描述是否清楚,先谢谢大家,解决问题后马上揭帖
(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=900SELECT 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 行受影响)
*/
(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=900SELECT
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 行受影响)
*/