如何实现仅在日期范围重叠时按ID聚合行的SQL查询

以下是满足需求的SQL查询，适用于支持窗口函数的SQL数据库（如MySQL 8+、PostgreSQL、SQL Server等）：

WITH ranked_skills AS (
    SELECT 
        ID,
        StartDate,
        EndDate,
        Skill,
        -- 标记重叠区间的分组：当前行开始日期 <= 上一行结束日期则归为同一组
        SUM(CASE WHEN StartDate <= LAG(EndDate) OVER (PARTITION BY ID ORDER BY StartDate) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ID ORDER BY StartDate) AS group_id
    FROM Skills
),
grouped_skills AS (
    SELECT 
        ID,
        MIN(StartDate) AS StartDate,
        MAX(EndDate) AS EndDate,
        group_id
    FROM ranked_skills
    GROUP BY ID, group_id
)
SELECT 
    gs.ID,
    gs.StartDate,
    gs.EndDate,
    s.Skill
FROM grouped_skills gs
JOIN Skills s 
    ON gs.ID = s.ID 
    AND gs.EndDate = s.EndDate
    -- 确保取到该分组最大EndDate对应的Skill（若同EndDate有多条，取StartDate最晚的那条）
    AND s.StartDate = (SELECT MAX(StartDate) FROM Skills WHERE ID = gs.ID AND EndDate = gs.EndDate)
ORDER BY gs.ID, gs.StartDate;

逻辑说明 #

1. 标记重叠分组：在ranked_skills CTE中，按ID分组、StartDate排序，用LAG函数获取上一行的EndDate。若当前行StartDate小于等于上一行EndDate，说明区间重叠，归为同一组；否则开启新分组，通过累加判断结果生成group_id。
2. 聚合区间信息：在grouped_skills CTE中，按ID和group_id聚合，提取每组最早的StartDate、最晚的EndDate。
3. 匹配对应技能：将聚合结果与原表关联，定位到每个分组最大EndDate对应的Skill，确保取到的是和最新结束日期绑定的技能。

执行后将得到预期结果：

1, 2021-01-01, 2021-12-31, A
1, 2022-01-01, 2022-12-31, B
2, 2021-01-01, 2022-12-31, B
3, 2021-01-01, 2023-12-31, C
4, 2021-01-01, 2021-12-31, A
4, 2022-01-01, 2023-12-31, C

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