(转)合并时间按段sql-备忘
假设有一张考勤单，经常需要将连续或重叠的时间段合并。在做简单查询时这可能成为问题，所以要当心，这个问题并不容易领会或理解：
?
CREATE TABLE Timesheets
?
(task_id CHAR(10) NOT NULL PRIMARY KEY,
?
?start_date DATE NOT NULL,
?
?end_date DATE NOT NULL,
?
?CHECK(start_date <= end_date));
?
INSERT INTO Timesheets
?
VALUES (1, ?'1997-01-01', '1997-01-03'),
?
? ? ? ?(2, ?'1997-01-02', '1997-01-04'),
?
? ? ? ?(3, ?'1997-01-04', '1997-01-05'),
?
? ? ? ?(4, ?'1997-01-06', '1997-01-09'),
?
? ? ? ?(5, ?'1997-01-09', '1997-01-09'),
?
? ? ? ?(6, ?'1997-01-09', '1997-01-09'),
?
? ? ? ?(7, ?'1997-01-12', '1997-01-15'),
?
? ? ? ?(8, ?'1997-01-13', '1997-01-14'),
?
? ? ? ?(9, ?'1997-01-14', '1997-01-14'),
?
? ? ? ?(10, '1997-01-17', '1997-01-17');
?
解惑 #1
?
SELECT T1.start_date, MAX(T2.end_date) FROM Timesheets AS T1, Timesheets AS T2 WHERE T1.start_date <= T2.end_date AND NOT EXISTS (SELECT * FROM Timesheets AS T3, Timesheets AS T4 WHERE T3.end_date < T4.start_date AND T3.start_date >= T1.start_date AND T3.end_date <= T2.end_date AND T4.start_date >= T1.start_date AND T4.end_date <= T2.end_date AND NOT EXISTS (SELECT * FROM Timesheets AS T5 WHERE T5.start_date BETWEEN T3.start_date AND T3.end_date AND T5.end_date BETWEEN T4.start_date AND T4.end_date)) GROUP BY T1.start_dateHAVING T1.start_date = MIN(t2.start_date);?结果
?
start_date ? ?end date
?
======================
?
1997-01-01 ?1997-01-05
?
1997-01-06 ?1997-01-09
?
1997-01-12 ?1997-01-15
?
1997-01-17 ?1997-01-17
?
结果
?
start_date ? ?end date
?
======================
?
1997-01-01 ?1997-01-05
?
1997-01-06 ?1997-01-09
?
1997-01-12 ?1997-01-15
?
1997-01-17 ?1997-01-17
?
解惑 #2
?
这个查询很长，但是检查一下查询时间。
?
SELECT X.start_date, MIN(Y.end_date) AS end_date FROM (SELECT T1.start_date FROM Timesheets AS T1 LEFT OUTER JOIN Timesheets AS T2 ON T1.start_date > T2.start_date AND T1.start_date <= T2.end_date GROUP BY T1.start_date HAVING COUNT(T2.start_date) = 0) AS X(start_date) INNER JOIN (SELECT T3.end_date FROM Timesheets AS T3 LEFT OUTER JOIN Timesheets AS T4 ON T3.end_date >= T4.start_date AND T3.end_date < T4.end_date GROUP BY T3.end_date HAVING COUNT(T4.start_date) = 0) AS Y(end_date) ON X.start_date <= Y.end_date GROUP BY X.start_date;?结果
?
start_date ? ?end date
?
=======================
?
1997-01-01 ? 1997-01-05
?
1997-01-06 ? 1997-01-09
?
1997-01-12 ? 1997-01-15
?
1997-01-17 ? 1997-01-17
?
结果
?
start_date ? ?end date
?
=======================
?
1997-01-01 ? 1997-01-05
?
1997-01-06 ? 1997-01-09
?
1997-01-12 ? 1997-01-15
?
1997-01-17 ? 1997-01-17
?
解惑 #3
?
SELECT X.start_date, MIN(X.end_date) AS end_date FROM (SELECT T1.start_date, T2.end_date FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3 WHERE T1.end_date <= T2.end_date GROUP BY T1.start_date, T2.end_date HAVING MAX (CASE WHEN (T1.start_date > T3.start_date AND T1.start_date <= T3.end_date) OR (T2.end_date >= T3.start_date AND T2.end_date < T3.end_date) THEN 1 ELSE 0 END) = 0) AS X GROUP BY X.start_date;
?
结果
start_date ? ?end_date
?
========================
?
1997-01-01 ? 1997-01-05
?
1997-01-06 ? 1997-01-09
?
1997-01-12 ? 1997-01-15
?
1997-01-17 ? 1997-01-17
这个小小的查询中包含了很多逻辑。
