SQL技术实现：将多条日期记录转换为单个日期范围（请假数据场景）

Alright, let's work through this problem together. You've got two tables tracking leave requests, and you need to roll up those individual non-canceled date entries into clean, continuous date ranges. Here's a step-by-step solution:

先明确表结构与现有数据 #

Leave Request 表 #

Leave Request Detail 表 #

核心需求拆解 #

我们需要先过滤掉标记为canceled='yes'的无效记录（假设最后一条的...代表非取消状态，若为取消则自动排除），再把剩下的离散日期中连续的部分合并成单个日期范围。

实现思路 #

利用窗口函数ROW_NUMBER()给每个有效日期排序，通过计算日期与序号的偏移量来识别连续日期组（连续日期的偏移量会完全一致），最后按组聚合得到每个连续区间的起止日期，再关联主表信息。

完整SQL语句 #

WITH valid_leave_dates AS (
    -- 第一步：筛选出未取消的有效请假日期
    SELECT 
        Req_Detail_Id,
        start_date,
        end_date
    FROM "Leave Request Details"
    WHERE canceled <> 'yes' -- 可根据实际调整，比如处理NULL或其他取消标识
),
date_groups AS (
    -- 第二步：为连续日期生成统一分组标识
    SELECT 
        Req_Detail_Id,
        start_date,
        end_date,
        DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY Req_Detail_Id ORDER BY start_date), start_date) AS group_id
    FROM valid_leave_dates
)
-- 第三步：关联主表，聚合得到连续日期范围
SELECT 
    lr.Leave_Req_Id,
    MIN(dg.start_date) AS range_start,
    MAX(dg.end_date) AS range_end
FROM "Leave Request" lr
JOIN date_groups dg ON lr.Leave_Req_Id = dg.Req_Detail_Id
GROUP BY lr.Leave_Req_Id, dg.group_id
ORDER BY range_start;

预期结果 #

针对你的示例数据，执行后会得到两个独立的连续范围：

1. 2013-01-05 至 2013-01-06（两天连续且未取消）
2. 2013-01-08 单独成一个范围（因01-07被取消，与前后日期不连续）

如果最后一条记录的canceled为yes，该日期会被过滤，结果仅保留第一个连续范围。

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