Chinaunix首页 | 论坛 | 博客
  • 博客访问: 58885
  • 博文数量: 20
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 282
  • 用 户 组: 普通用户
  • 注册时间: 2014-11-20 17:23
个人简介

我的博客园;http://www.cnblogs.com/geekpaul/

文章分类

全部博文(20)

文章存档

2015年(7)

2014年(13)

我的朋友

分类: Mysql/postgreSQL

2014-11-26 22:45:33

假设表结构如下所示:

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


阅读(721) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~