【难题】如何按时间分组

Question

DECLARE @t TABLE(id INT IDENTITY(1,1),WorkTime DATETIME)
INSERT @t(WorkTime) SELECT DATEADD(hh,-5,GETDATE())
UNION ALL SELECT DATEADD(dd,-3,GETDATE())
UNION ALL SELECT GETDATE()
UNION ALL SELECT GETDATE()
UNION ALL SELECT GETDATE()
UNION ALL SELECT DATEADD(n,130,GETDATE())SELECT '当天的第' + RTRIM(GID) + '个20分钟' [20分钟序列],
CONVERT(VARCHAR(11),DATEADD(n,MIN(TimeSpan),'2006-8-1'),114) [此20分钟内出最早发生事件的时间],COUNT(*) [此20分钟内发生事件数] FROM (SELECT CASE WHEN DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20= 0 THEN
DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),WorkTime,114))/20
ELSE
(DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),WorkTime,114)) - 
(DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20)
)/20 +1
END GID,DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),WorkTime,114)) TimeSpan
FROM @t WHERE WorkTime BETWEEN '2006-8-12' AND '2006-8-15' /*假设在此时间段查找*/ AND DATEPART(hh,WorkTime) BETWEEN 8 AND 17 /*早8点至晚5点之间*/
) x
GROUP BY GID

Answer 1

我要查询的表和字段在哪里声明呢？
比如我的表名叫tbtest，字段叫worktime

Answer 2

还有
SELECT '当天的第' + RTRIM(GID) + '个20分钟' [20分钟序列],
CONVERT(VARCHAR(11),DATEADD(n,MIN(TimeSpan),'2006-8-1'),114) [此20分钟内出最早发生事件的时间],COUNT(*) [此20分钟内发生事件数] FROM (SELECT CASE WHEN DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20= 0 THEN
DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),WorkTime,114))/20
ELSE
(DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),WorkTime,114)) - 
(DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20)
)/20 +1
END GID,DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),WorkTime,114)) TimeSpan这里面的2006-8-1是起甚么作用的？

Answer 3

把表变量声明的语句去掉.
在查询语句中,将@t改为 tbtest即可.2006-8-1没什么用,你改成 2000-1-1也一样,但要注意语句中的 2006-8-1一起替换.
加上这个为了让选下时间段里的 所有时间转换成同一天,构成同一天之后,即长日期格式后运用datediff及dateadd函数进行一些处理. 
理论上,不加date部分(即2006-8-1)日期处理函数会报错,加上它让语义更明确.
而实际不加也可以,系统会自动补成1900-01-01,即把date部分补成替换NULL的默认值.select year('12:00:00.000')你一看就明白了.

Answer 4

SELECT '当天的第' + RTRIM(GID) + '个20分钟' [20分钟序列],
CONVERT(VARCHAR(11),DATEADD(n,MIN(TimeSpan),'2006-8-1'),114) [此20分钟内出最早发生事件的时间],COUNT(*) [此20分钟内发生事件数] FROM (SELECT CASE WHEN DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),vatddate,114)) % 20= 0 THEN
DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),vatddate,114))/20
ELSE
(DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),vatddate,114)) - 
(DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),vatddate,114)) % 20)
)/20 +1
END GID,DATEDIFF(n,'2006-8-1','2006-8-1 ' + CONVERT(varchar(11),vatddate,114)) TimeSpan
FROM tbdecvat WHERE vatddate BETWEEN '2006-8-12' AND '2006-8-15' /*假设在此时间段查找*/ AND DATEPART(hh,vatddate) 
BETWEEN 8 AND 17 /*早8点至晚5点之间*/
) x
GROUP BY GID

Answer 5

还能再精简一下么？我还是不是很明白，上面的查询确实可以检索出结果来，我不需要gid字段，只需要看是哪个时间段就可以了时间段                           数量
2006-8-1 08:00  2006-8-1 08:20     3
2006-8-1 08:20  2006-8-1 08:40     8
2006-8-1 08:40  2006-8-1 09:00     7

Answer 6

CREATE TABLE tbTest(id INT IDENTITY(1,1),WorkTime DATETIME)
INSERT tbTest(WorkTime) SELECT DATEADD(hh,-5,GETDATE())
UNION ALL SELECT DATEADD(dd,-3,GETDATE())
UNION ALL SELECT GETDATE()
UNION ALL SELECT GETDATE()
UNION ALL SELECT GETDATE()
UNION ALL SELECT DATEADD(n,130,GETDATE())
DECLARE @t DATETIME,@t1 DATETIME
SELECT @T='08:00:00.000',@T1='17:59:59.000' 
DECLARE @i INT,@s VARCHAR(8000),@k INT,@n INT
SELECT @i=DATEDIFF(n,@t,@t1)/20+1,@k=0
SELECT @n=DATEDIFF(n,'00:00:00.000',@t)/20+1SET @s='SELECT 0 Id '
SELECT @k=@k+20 ,@s=@s + ' UNION SELECT ' + RTRIM(@k)  FROM Sysobjects
--SELECT @s='SELECT TOP ' + RTRIM(@i) + ' DATEADD(n,id,''' + CONVERT(VARCHAR(10),@t,120) + ''') TimeID INTO tmp FROM (' + @s + ')x WHERE DATEDIFF(n,''' + CONVERT(varchar,@t,120) + ''',DATEADD(n,ID*20,''' + CONVERT(varchar,@t,120) + '''))>=0'
SELECT @s='SELECT TOP ' + RTRIM(@i) + ' DATEADD(n,id,''' + CONVERT(VARCHAR(10),@t,120) + ''') TimeID,identity(int,'+RTRIM(@n) + ',1) ID INTO tmp FROM (' + @s + ')x WHERE DATEDIFF(n,''' + CONVERT(varchar,@t,120) + ''',DATEADD(n,ID,''1900-01-01 00:00:00''))>=0'
EXEC(@s)
SELECT CONVERT(VARCHAR(11),TimeID,114),ISNULL(CNT,0)
FROM tmp a
LEFT JOIN
(SELECT CASE WHEN DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20= 0 THEN
DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114))/20
ELSE
(DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) - 
(DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20)
)/20 +1
END GID,COUNT(*) CNT
FROM tbTest WHERE WorkTime BETWEEN '2006-8-12' AND '2006-8-15' /*假设在此时间段查找*/ AND DATEPART(hh,WorkTime) BETWEEN 8 AND 17 /*早8点至晚5点之间*/
GROUP BY 
CASE WHEN DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20= 0 THEN
DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114))/20
ELSE
(DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) - 
(DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20)
)/20 +1
END ) b
ON a.ID=b.GidDROP TABLE tbTest,tmp
/*结果
08:00:00:00 0
08:20:00:00 0
08:40:00:00 0
09:00:00:00 1
09:20:00:00 0
09:40:00:00 0
10:00:00:00 0
10:20:00:00 0
10:40:00:00 0
11:00:00:00 0
11:20:00:00 0
11:40:00:00 0
12:00:00:00 0
12:20:00:00 0
12:40:00:00 0
13:00:00:00 0
13:20:00:00 0
13:40:00:00 0
14:00:00:00 3
14:20:00:00 0
14:40:00:00 0
15:00:00:00 0
15:20:00:00 0
15:40:00:00 0
16:00:00:00 1
16:20:00:00 0
16:40:00:00 0
17:00:00:00 0
17:20:00:00 0
17:40:00:00 0
*/

Answer 7

怎么越改越复杂了啊？
我用下面的语句已经查询出结果了，我想问能不能把语句改进一下 结果按上面的显示SELECT '当天的第' + RTRIM(GID) + '个20分钟' [20分钟序列],
CONVERT(VARCHAR(11),DATEADD(n,MIN(TimeSpan),2006-7-1),114) [此20分钟内出最早发生事件的时间],
COUNT(*) [此20分钟内发生事件数] 
FROM (SELECT CASE WHEN DATEDIFF(n,2006-7-1,2006-7-1 + CONVERT(varchar(11),worktime,114)) % 20= 0 THEN
DATEDIFF(n,2006-7-1,2006-7-1 + CONVERT(varchar(11),worktime,114))/20
ELSE
(DATEDIFF(n,2006-7-1,2006-7-1 + CONVERT(varchar(11),worktime,114)) - 
(DATEDIFF(n,2006-7-1,2006-7-1 + CONVERT(varchar(11),worktime,114)) % 20)
)/20 +1
END GID,DATEDIFF(n,2006-7-1,2006-7-1 + CONVERT(varchar(11),worktime,114)) TimeSpan
FROM tbtest WHERE worktime BETWEEN 2006-7-1 AND '2006-7-15' AND DATEPART(hh,worktime) 
BETWEEN 8 AND 17 /*早8点至晚5点之间*/
) x
GROUP BY GID

Answer 8

上面显示的全序列.
即不管某个20分钟时段有无work事件都显示出来.
如果你只显示有事件的时段,那么CREATE TABLE tbTest(id INT IDENTITY(1,1),WorkTime DATETIME)
INSERT tbTest(WorkTime) SELECT DATEADD(hh,-5,GETDATE())
UNION ALL SELECT DATEADD(dd,-3,GETDATE())
UNION ALL SELECT GETDATE()
UNION ALL SELECT GETDATE()
UNION ALL SELECT GETDATE()
UNION ALL SELECT DATEADD(n,130,GETDATE())SELECT GID,COUNT(1) FROM
(SELECT DATEADD(n,(CASE WHEN DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20= 0 THEN
DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114))/20
ELSE
(DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) - 
(DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20)
)/20 +1
END) * 20,'08:00:00.000') GID
FROM tbTest WHERE WorkTime BETWEEN '2006-8-12' AND '2006-8-15' /*假设在此时间段查找*/ AND DATEPART(hh,WorkTime) BETWEEN 8 AND 17 /*早8点至晚5点之间*/
) x GROUP BY GID
DROP TABLE tbTest

Answer 9

上面手误END) * 20,'08:00:00.000') GID
改为
END) * 20,'00:00:00.000') GID

Answer 10

怕了你了,两个一起写给你.
上面那种写法是所有天每20分钟的事件数量总合,用convert(vachar,gid,114)取出时间就可以了,为什么不能显示date部分,你自己想.下面这个统计每天每20分钟的CREATE TABLE tbTest(id INT IDENTITY(1,1),WorkTime DATETIME)
INSERT tbTest(WorkTime) SELECT DATEADD(hh,-5,GETDATE())
UNION ALL SELECT DATEADD(dd,-3,GETDATE())
UNION ALL SELECT DATEADD(dd,-1,GETDATE())
UNION ALL SELECT GETDATE()
UNION ALL SELECT GETDATE()
UNION ALL SELECT GETDATE()
UNION ALL SELECT DATEADD(n,130,GETDATE())SELECT DATEADD(n,GID * 20,DateID) ,COUNT(1) FROM
(SELECT CASE WHEN DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20= 0 THEN
DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114))/20
ELSE
(DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) - 
(DATEDIFF(n,'1900-1-1','1900-1-1 ' + CONVERT(varchar(11),WorkTime,114)) % 20)
)/20 +1
END GID,CONVERT(VARCHAR(10),WorkTime,120) DateID
FROM tbTest WHERE WorkTime BETWEEN '2006-8-12' AND '2006-8-15' /*假设在此时间段查找*/ AND DATEPART(hh,WorkTime) BETWEEN 8 AND 17 /*早8点至晚5点之间*/
) x GROUP BY GID,DATEID
DROP TABLE tbTest/*result
2006-08-13 15:20:00.000 1
2006-08-14 10:20:00.000 1
2006-08-14 15:20:00.000 3
2006-08-14 17:20:00.000 1*/

Answer 11

举个例子，我要统计 2006-8-12 到 2006-8-15这个时间段内，每天的8点到17点之间每20分钟的事件数量，结果类似：
2006-8-12 08:00  2006-8-12 08:20     3
2006-8-12 08:20  2006-8-12 08:40     8
2006-8-12 08:40  2006-8-12 09:00     7
……
2006-8-12 16:40  2006-8-12 17:00     7
2006-8-13 08:00  2006-8-13 08:20     17
2006-8-13 08:20  2006-8-13 08:40     8
2006-8-13 08:40  2006-8-13 09:00     7依次类推就相当于多个
select '2006-8-12 08:00:00',count(*) from tbtest
where worktime between '2006-8-12 08:00:00' and '2006-8-12 08:20:00'
select '2006-8-12 08:20:00',count(*) from tbtest
where worktime between '2006-8-12 08:20:00' and '2006-8-12 08:40:00'
的结果集合我只是不知道如何用一条语句把它写出来