Within Temptation

Touch guitar's programmer

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

假设表结构如下所示:

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

 

posted on 2014-11-26 23:03  Within_Temptation  阅读(640)  评论(0编辑  收藏  举报