SQL合並時間段的問題，sql合並

SQL合並時間段的問題，sql合並

假設表結構如下所示：

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