假設表結構如下所示:
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 X 10 GROUP BY X.start_date