请教一个非常复杂的时间分段查询
首先是原始表
doer dotime
a2012-1-1 0:09
a2012-1-1 1:09
a2012-1-1 2:09
a2012-1-1 3:09
a2012-1-1 4:09
a2012-1-1 5:09
a2012-1-1 6:09
a2012-1-1 7:09
a2012-1-1 8:09
a2012-1-1 9:09
a2012-1-1 10:09
a2012-1-1 11:09
a2012-1-1 12:09
a2012-1-1 13:09
a2012-1-1 14:09
a2012-1-1 15:09
a2012-1-1 16:09
a2012-1-1 17:09
a2012-1-1 18:09
a2012-1-1 19:09
a2012-1-1 20:09
a2012-1-1 21:09
a2012-1-1 22:09
a2012-1-1 23:09
b2012-1-1 0:20
b2012-1-1 2:20
b2012-1-1 4:20
b2012-1-1 6:20
b2012-1-1 8:20
b2012-1-1 10:20
b2012-1-1 12:20
b2012-1-1 14:20
b2012-1-1 16:20
b2012-1-1 18:20
b2012-1-1 20:20
b2012-1-1 22:20
把一天时间分成若干时间段，这里假设为6个，每4小时为一个时间段
针对每个Doer的每天的操作，将各个时间段的第一个时间填充到对应自段中，形成类似下面的表结构
doer 0-4 4-8 8-12...
a 2012-1-1 0:09 2012-1-1 4:09 2012-1-1 8:09 ...
b 2012-1-1 0:20 2012-1-1 4:20 2012-1-1 8:20 ...
[最优解释]
select doer,
[0-4]=min(case when datepart(hh,dotime) between 0 and 4 then dotime end),
[4-8]=min(case when datepart(hh,dotime) between 4 and 8 then dotime end),
[8-12]=min(case when datepart(hh,dotime) between 8 and 12 then dotime end)
..............
from tb group by doer
[其他解释]
;WITH c1(doer, dotime) AS
(
SELECT 'a','2012-1-1 0:09' UNION ALL
SELECT 'a','2012-1-1 1:09' UNION ALL
SELECT 'a','2012-1-1 2:09' UNION ALL
SELECT 'a','2012-1-1 3:09' UNION ALL
SELECT 'a','2012-1-1 4:09' UNION ALL
SELECT 'a','2012-1-1 5:09' UNION ALL
SELECT 'a','2012-1-1 6:09' UNION ALL
SELECT 'a','2012-1-1 7:09' UNION ALL
SELECT 'a','2012-1-1 8:09' UNION ALL
SELECT 'a','2012-1-1 9:09' UNION ALL
SELECT 'a','2012-1-1 10:09' UNION ALL
SELECT 'a','2012-1-1 11:09' UNION ALL
SELECT 'a','2012-1-1 12:09' UNION ALL
SELECT 'a','2012-1-1 13:09' UNION ALL
SELECT 'a','2012-1-1 14:09' UNION ALL
SELECT 'a','2012-1-1 15:09' UNION ALL
SELECT 'a','2012-1-1 16:09' UNION ALL
SELECT 'a','2012-1-1 17:09' UNION ALL
SELECT 'a','2012-1-1 18:09' UNION ALL
SELECT 'a','2012-1-1 19:09' UNION ALL
SELECT 'a','2012-1-1 20:09' UNION ALL
SELECT 'a','2012-1-1 21:09' UNION ALL
SELECT 'a','2012-1-1 22:09' UNION ALL
SELECT 'a','2012-1-1 23:09' UNION ALL
SELECT 'b','2012-1-1 0:20' UNION ALL
SELECT 'b','2012-1-1 2:20' UNION ALL
SELECT 'b','2012-1-1 4:20' UNION ALL
SELECT 'b','2012-1-1 6:20' UNION ALL
SELECT 'b','2012-1-1 8:20' UNION ALL
SELECT 'b','2012-1-1 10:20' UNION ALL
SELECT 'b','2012-1-1 12:20' UNION ALL
SELECT 'b','2012-1-1 14:20' UNION ALL
SELECT 'b','2012-1-1 16:20' UNION ALL
SELECT 'b','2012-1-1 18:20' UNION ALL
SELECT 'b','2012-1-1 20:20' UNION ALL
SELECT 'b','2012-1-1 22:20'
)
SELECT doer,
CAST(dotime AS DATE) [oDate],
(SELECT MIN(dotime) FROM c1 WHERE doer = d.doer AND CAST(dotime AS DATE)= CAST(d.dotime AS DATE) AND DATEPART("HOUR", dotime) BETWEEN 0 AND 4 ) [0-4],
(SELECT MIN(dotime) FROM c1 WHERE doer = d.doer AND CAST(dotime AS DATE)= CAST(d.dotime AS DATE) AND DATEPART("HOUR", dotime) BETWEEN 4 AND 8 ) [4-8],
(SELECT MIN(dotime) FROM c1 WHERE doer = d.doer AND CAST(dotime AS DATE)= CAST(d.dotime AS DATE) AND DATEPART("HOUR", dotime) BETWEEN 8 AND 12 ) [8-12],
(SELECT MIN(dotime) FROM c1 WHERE doer = d.doer AND CAST(dotime AS DATE)= CAST(d.dotime AS DATE) AND DATEPART("HOUR", dotime) BETWEEN 12 AND 16 ) [12-16],
(SELECT MIN(dotime) FROM c1 WHERE doer = d.doer AND CAST(dotime AS DATE)= CAST(d.dotime AS DATE) AND DATEPART("HOUR", dotime) BETWEEN 16 AND 20 ) [16-20],
(SELECT MIN(dotime) FROM c1 WHERE doer = d.doer AND CAST(dotime AS DATE)= CAST(d.dotime AS DATE) AND DATEPART("HOUR", dotime) BETWEEN 20 AND 24 ) [20-24]
FROM c1 d
GROUP BY doer, CAST(dotime AS DATE)
doer oDate 0-4 4-8 8-12 12-16 16-20 20-24
---- ---------- -------------- -------------- -------------- -------------- -------------- --------------
a 2012-01-01 2012-1-1 0:09 2012-1-1 4:09 2012-1-1 10:09 2012-1-1 12:09 2012-1-1 16:09 2012-1-1 20:09
b 2012-01-01 2012-1-1 0:20 2012-1-1 4:20 2012-1-1 10:20 2012-1-1 12:20 2012-1-1 16:20 2012-1-1 20:20
(2 行受影响)
..............
from tb group by doer,convert(varchar(10),dotime,120)
[其他解释]
十分感谢楼上的，我发现我忽略的一个要求，每天都要有一条这样的记录，加一个日期字段，行成的表应该类似下面的
doer rq 0-4 4-8 8-12...
a 2012-1-1 2012-1-1 0:09 2012-1-1 4:09 2012-1-1 8:09 ...
a 2012-1-2 2012-1-2 0:09 2012-1-2 4:09 2012-1-2 8:09 ...
.......................
b 2012-1-1 2012-1-1 0:20 2012-1-1 4:20 2012-1-1 8:20 ...
