程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> SQL合並時間段的問題,sql合並

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

編輯:MySQL綜合教程

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

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved