请求提供合并存在包含关系日期范围的SQL语句

需求说明 #

当某条记录的开始日期落在其他记录的起止日期之间时，合并存在重叠或包含关系的日期范围，最终输出不重叠的连续日期区间。

样本数据 #

合并逻辑 #

• 数据按开始日期升序排列
• 记录1（01/01/17-31/01/18）不与任何其他记录重叠或包含，保留原区间
• 记录2至6存在互相包含或重叠的情况，合并为一个区间：开始日期取最早的01/02/18，结束日期取最晚的06/10/22

期望输出 #

SQL实现方案 #

方法1：使用窗口函数（适用于MySQL8+、PostgreSQL、SQL Server等） #

WITH ranked_dates AS (
    SELECT 
        start_date,
        end_date,
        -- 标记每个区间是否为新组：当前start_date大于之前所有区间的最大end_date则为新组
        SUM(CASE WHEN start_date > MAX(end_date) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 1 ELSE 0 END) OVER (ORDER BY start_date) AS group_id
    FROM your_table
),
grouped_intervals AS (
    SELECT
        group_id,
        MIN(start_date) AS merged_start_date,
        MAX(end_date) AS merged_end_date
    FROM ranked_dates
    GROUP BY group_id
)
SELECT merged_start_date, merged_end_date
FROM grouped_intervals
ORDER BY merged_start_date;

语句说明

1. ranked_dates CTE：通过窗口函数计算每个记录的所属分组。若当前记录的开始日期晚于之前所有记录的最大结束日期，则标记为新组；否则归入同一组。
2. grouped_intervals CTE：对每个分组取最小开始日期和最大结束日期，得到合并后的区间。
3. 最后按合并后的开始日期排序输出。

方法2：兼容旧版数据库（不支持窗口函数，如MySQL5.x） #

SELECT
    MIN(t1.start_date) AS merged_start_date,
    MAX(t1.end_date) AS merged_end_date
FROM your_table t1
LEFT JOIN your_table t2 
    ON t2.start_date < t1.start_date 
    AND t2.end_date >= t1.start_date
GROUP BY t1.start_date - COALESCE((SELECT COUNT(*) FROM your_table t3 WHERE t3.start_date < t1.start_date AND t3.end_date >= t1.start_date), 0)
ORDER BY merged_start_date;

内容的提问来源于stack exchange，提问作者Jigar Gorakhiya