SQL合并时间段的问题

最新推荐文章于 2024-02-18 18:25:58 发布

原创最新推荐文章于 2024-02-18 18:25:58 发布 · 768 阅读

1 ·

CC 4.0 BY-SA版权

SQL&PL/SQL 专栏收录该内容

3 篇文章

订阅专栏

本文探讨了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


    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