如何使用T-SQL将SQL Server休假记录配对并标记无效记录

要解决休假（Leave）和返岗（Return）记录的配对问题，同时标记无法配对的记录，可以通过窗口函数生成序号+全外连接的方式实现，逻辑清晰且高效。

核心思路 #

1. 分别提取员工的休假、返岗记录，按员工分组、时间升序生成序号——确保最早的休假对应最早的未配对返岗，以此类推。
2. 通过全外连接关联两类记录，匹配相同员工、相同序号的记录，同时标记无匹配的冗余记录。

完整实现脚本 #

WITH LeaveRecords AS (
    -- 提取所有休假记录并按员工+时间生成序号
    SELECT 
        pmah_emp,
        pmah_eff_date AS LeaveDate,
        ROW_NUMBER() OVER (PARTITION BY pmah_emp ORDER BY pmah_eff_date ASC) AS LeaveSeq
    FROM #pmachist
    WHERE codeStatus = 'Leave'
),
ReturnRecords AS (
    -- 提取所有返岗记录并按员工+时间生成序号
    SELECT 
        pmah_emp,
        pmah_eff_date AS ReturnDate,
        ROW_NUMBER() OVER (PARTITION BY pmah_emp ORDER BY pmah_eff_date ASC) AS ReturnSeq
    FROM #pmachist
    WHERE codeStatus = 'Return'
)
-- 关联两类记录并标记匹配状态
SELECT 
    COALESCE(l.pmah_emp, r.pmah_emp) AS EmployeeID,
    l.LeaveDate,
    r.ReturnDate,
    CASE 
        WHEN l.LeaveDate IS NULL THEN '无对应休假的返岗记录'
        WHEN r.ReturnDate IS NULL THEN '无对应返岗的休假记录'
        ELSE '已配对'
    END AS MatchStatus
FROM LeaveRecords l
FULL OUTER JOIN ReturnRecords r 
    ON l.pmah_emp = r.pmah_emp 
    AND l.LeaveSeq = r.ReturnSeq
ORDER BY COALESCE(l.pmah_emp, r.pmah_emp), COALESCE(l.LeaveDate, r.ReturnDate);

脚本说明 #

• CTE分区排序：LeaveRecords和ReturnRecords分别为每个员工的休假、返岗记录按时间升序分配序号，保证时间顺序上的配对逻辑。
• 全外连接：保留所有记录，既包含能配对的条目，也保留无匹配的冗余记录。
• 状态标记：通过CASE语句明确区分三种状态：已配对、无对应返岗的休假、无对应休假的返岗。

扩展用法 #

如果只需要保留已配对的记录，只需将FULL OUTER JOIN改为INNER JOIN即可，过滤掉所有冗余条目。

测试结果示例 #

针对你提供的测试数据，执行脚本后会输出如下结果（仅展示关键字段）：

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