假设表结构如下所示:
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
-
SELECT X.start_date, MIN(X.end_date) as end_date
-
FROM (SELECT T1.start_date,T2.end_date
-
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
-
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
阅读(721) | 评论(0) | 转发(0) |