如何用SQL合并连续日期的不同请假申请为单条记录

要将同一申请人的连续日期请假申请合并为单条记录，核心是通过窗口函数识别连续日期段，再分组聚合。以下是具体实现方案：

实现步骤 #

1. 去重基础数据：先确保每个请假申请仅保留一条唯一记录（避免关联明细表产生重复行）
2. 标记连续日期分组：用窗口函数给连续的日期记录打上相同的分组标识
3. 分组聚合合并记录：按分组标识和申请人聚合，取最早的开始日期与最晚的结束日期

完整SQL代码 #

WITH UniqueLeave AS (
    -- 获取去重后的请假记录
    SELECT DISTINCT
        LT.Reques_id,
        LT.LeaveFromdate, 
        LT.LeaveTodate   
    FROM LeaveTbl LT   
    JOIN LeaveTbl_detail LTD ON LT.Request_id = LTD.Request_id
),
LeaveGroups AS (
    -- 给连续日期的记录标记相同分组
    SELECT
        Reques_id,
        LeaveFromdate,
        LeaveTodate,
        DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY Reques_id ORDER BY LeaveFromdate), LeaveFromdate) AS group_key
    FROM UniqueLeave
)
-- 分组聚合得到合并后的记录
SELECT
    Reques_id,
    MIN(LeaveFromdate) AS MergedLeaveFrom,
    MAX(LeaveTodate) AS MergedLeaveTo
FROM LeaveGroups
GROUP BY Reques_id, group_key
ORDER BY Reques_id, MergedLeaveFrom;

数据库语法适配 #

如果使用MySQL，DATEADD需替换为DATE_SUB，修改后的分组标识计算语句为：

DATE_SUB(LeaveFromdate, INTERVAL ROW_NUMBER() OVER (PARTITION BY Reques_id ORDER BY LeaveFromdate) DAY) AS group_key

内容的提问来源于stack exchange，提问作者G.vigneshwari