-- 下面的例子演示了,如何按 20 分钟 一个间隔汇总数据 DECLARE @T TABLE ( Amount DECIMAL(18, 2) -- 数量 , Total DECIMAL(18, 0) -- 金额 , GenerateDate DATETIME) -- 产生时间 INSERT INTO @T VALUES (12, 100, '2009-10-10 08:00:00') INSERT INTO @T VALUES (21, 269, '2009-10-10 08:10:00') INSERT INTO @T VALUES (32, 310, '2009-10-10 08:15:00') INSERT INTO @T VALUES (32, 310, '2009-10-10 08:21:00') INSERT INTO @T VALUES (40, 512, '2009-10-10 08:41:00') INSERT INTO @T VALUES (51, 619, '2009-10-10 08:42:00') INSERT INTO @T VALUES (69, 989, '2009-10-10 09:12:00') INSERT INTO @T VALUES (71, 666, '2009-10-10 09:19:00') INSERT INTO @T VALUES (71, 666, '2009-10-10 10:11:00') -- 按 20 分钟的间隔进行统计 DECLARE @Minute INT SET @Minute = 20 -- @Minute 不能超过 30 超过了可能会时间溢出 SELECT StartDate , EndDate , Amount = SUM(Amount) , Total = SUM(Total) FROM ( SELECT StartDate = CAST(CONVERT(VARCHAR(14), GenerateDate, 120) + CAST(DATEPART(mi, GenerateDate) / @Minute * @Minute AS VARCHAR(2)) AS DATETIME) -- 间隔开始时间 , EndDate = CAST(CONVERT(VARCHAR(14), GenerateDate, 120) + CAST(DATEPART(mi, GenerateDate) / @Minute * @Minute + @Minute - 1 AS VARCHAR(2)) + ':59' AS DATETIME) -- 间隔结束时间 , Amount , Total FROM @T) A GROUP BY StartDate, EndDate -- 如果要按每一小时的间隔进行汇总统计就更简单了 SELECT CONVERT(VARCHAR(13), GenerateDate, 120) AS YourDate, SUM(Amount) AS Amount, SUM(Total) AS Total FROM @T GROUP BY CONVERT(VARCHAR(13), GenerateDate, 120) -- 使用到的技巧 -- 这里主要是用到的 Int 相除时会舍掉余数的特点 -- 如分钟 20 / 20 * 20 = 20 和 25 / 20 * 20 = 20 都能得到同样的结果,利用这个的特点就能方便的求到汇总的时间段