如何用SQL查询重叠日期范围对应的所有离散独立区间

实现思路 #

• 第一步：提取所有分界点：将所有输入区间的start日期、end + 1日期（适配闭区间计算逻辑）统一收集为单个时间点集合，去重后按升序排序
• 第二步：生成候选区间：将相邻的两个时间点配对，前一个作为候选区间start，后一个减1天作为候选区间end
• 第三步：过滤有效区间：仅保留至少被1个原始输入区间完全覆盖的候选区间，即为最终的不重叠离散区间

通用SQL代码示例（MySQL 8.0+版本） #

假设输入数据存储在date_ranges表，字段为start_date、end_date：

WITH all_points AS (
    -- 收集所有分界时间点
    SELECT start_date AS point FROM date_ranges
    UNION
    SELECT DATE_ADD(end_date, INTERVAL 1 DAY) AS point FROM date_ranges
),
ordered_points AS (
    -- 给时间点按顺序编号
    SELECT 
        point,
        ROW_NUMBER() OVER (ORDER BY point) AS rn
    FROM all_points
),
candidate_ranges AS (
    -- 拼接相邻时间点生成候选区间
    SELECT 
        p1.point AS range_start,
        DATE_SUB(p2.point, INTERVAL 1 DAY) AS range_end
    FROM ordered_points p1
    JOIN ordered_points p2 ON p1.rn = p2.rn - 1
)
-- 筛选被原始区间覆盖的有效区间
SELECT DISTINCT cr.range_start AS `start`, cr.range_end AS `end`
FROM candidate_ranges cr
JOIN date_ranges dr 
    ON cr.range_start >= dr.start_date 
    AND cr.range_end <= dr.end_date
ORDER BY cr.range_start;

示例验证 #

对应题目给出的输入：

start           end
2010-01-01      2010-01-31
2010-01-10      2010-02-10

第一步收集到的分界点为：2010-01-01、2010-01-10、2010-02-01、2010-02-11，最终输出结果和题目预期完全一致。

适配说明 #

• 其他数据库仅需修改日期加减语法即可：PostgreSQL用end_date + INTERVAL '1 day'，Oracle用end_date + 1
• 支持任意数量的输入日期区间，无数量上限限制

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