假设表结构如下所示：
Tsheets | ||
字段名 | 字段类型 | 约束 |
id | CHAR(10) | PRIMARY KEY |
start_date | DATE | CHECK(start_date<= end_date) |
end_date | DATE |
|
输入为：
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'
输出为：
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
1 SELECT X.start_date, MIN(X.end_date) as end_date 2 FROM (SELECT T1.start_date,T2.end_date 3 FROM Tsheets AS T1,Tsheets AS T2,Tsheets AS T3 WHERE T1.end_date <= T2.end_date GROUP BY T1.start_date,T2.end_date 4 HAVING MAX (CASE 5 WHEN (T1.start_date > T3.start_date 6 AND T1.start_date <= T3.end_date) 7 OR(T2.end_date >= T3.start_date 8 AND T2.end_date < T3.end_date) 9 THEN 1 ELSE 0 END) = 0) AS X10 GROUP BY X.start_date