SQL Server中如何按指定条件合并连续数据行？

原始表结构与数据 #

SQL Server中有一张名为Department的表，包含4列，数据如下：

合并条件 #

需要按以下规则合并多行记录：

• 相同的staffNumber
• 相同的department
• 连续记录中当前行的endDate与下一行的startDate天数差为0

尝试的SQL及问题 #

我尝试了以下SQL查询，但未得到预期输出：

WITH DeptData_CTE AS 
(
    SELECT 
        staffNumber,
        department,
        startDate,
        endDate,
        LEAD(startDate) OVER (PARTITION BY staffNumber, department ORDER BY startDate) AS nextstartDate,
        LAG(endDate) OVER (PARTITION BY staffNumber, department ORDER BY startDate) AS prevendDate
    FROM 
        department
),
MergedData_CTE AS 
(
    SELECT 
        staffNumber,
        department,
        MIN(startDate) AS startDate,
        MAX(endDate) AS endDate
    FROM 
        DeptData_CTE
    -- We merge rows where previous expiry date and current effective date are consecutive
    WHERE 
        prevendDate IS NULL 
        OR DATEDIFF(day, prevendDate, startDate) != 0
    GROUP BY 
        staffNumber, 
        department
)
-- Final selection of rows
SELECT 
    staffNumber,
    department,
    startDate,
    endDate
FROM 
    MergedData_CTE
ORDER BY 
    staffNumber, 
    department, 
    startDate;

得到的合并行startDate符合预期，但endDate不符合——原查询直接按staffNumber和department分组，会把同一员工同一部门的所有记录（即使中间有断开）都合并成一条，比如员工100的A部门有两段时间，原查询会错误合并。

预期输出 #

正确的合并结果应该是：

注：员工105的两条B部门记录日期差为1天，所以不合并。

解决方案SQL #

可以通过生成连续分组标识来实现正确合并，具体如下：

WITH DeptGroups AS (
    SELECT 
        staffNumber,
        department,
        startDate,
        endDate,
        -- 生成分组ID：当前记录与前一条不连续时，组号+1
        SUM(CASE 
            WHEN LAG(endDate) OVER (PARTITION BY staffNumber, department ORDER BY startDate) IS NULL 
                OR DATEDIFF(day, LAG(endDate) OVER (PARTITION BY staffNumber, department ORDER BY startDate), startDate) != 0
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY staffNumber, department ORDER BY startDate) AS GroupId
    FROM Department
)
SELECT 
    staffNumber,
    department,
    MIN(startDate) AS startDate,
    MAX(endDate) AS endDate
FROM DeptGroups
GROUP BY staffNumber, department, GroupId
ORDER BY staffNumber, department, startDate;

逻辑说明 #

1. 用LAG窗口函数获取当前记录的上一条同员工同部门记录的endDate
2. 判断当前记录与上一条是否连续（天数差为0），不连续则生成新的分组ID
3. 按staffNumber、department和GroupId分组，取每组的最小startDate和最大endDate，得到正确的合并结果

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