SQL Server如何实现两表按列值匹配一对一抵消删除行

核心实现逻辑：为两张表中相同匹配字段值的重复记录生成组内递增行号，只有匹配字段值完全一致、且组内行号也一一对应的记录才会被删除，严格实现「B表有N条匹配记录，就仅删除A表N条对应记录」的需求。

实现代码 #

WITH LeaveWithRowNum AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY applicant_name, calender_date, timeslot
            -- 若有明确匹配优先级（如按申请时间先后抵消），将下方排序规则替换为对应业务字段即可
            ORDER BY (SELECT 1)
        ) AS match_rn
    FROM @tmp_leave
),
CancelLeaveWithRowNum AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY applicant_name, calender_date, timeslot
            ORDER BY (SELECT 1)
        ) AS match_rn
    FROM @tmp_cancelLeave
)
DELETE l
FROM LeaveWithRowNum l
INNER JOIN CancelLeaveWithRowNum c
    ON l.applicant_name = c.applicant_name
    AND l.calender_date = c.calender_date
    AND l.timeslot = c.timeslot
    AND l.match_rn = c.match_rn;

示例数据验证 #

代入提供的测试数据：

• 表A中2021-12-24 11:00:00的2条记录组内行号为1、2，11:30:00的2条记录组内行号为1、2，其余单条记录组内行号均为1
• 表B中2021-12-24 11:00:00的2条记录组内行号为1、2，9:30:00、11:30:00的单条记录组内行号均为1
• 匹配删除后表A剩余记录为10:00:00、10:30:00、11:30:00（组内行号为2的记录），共3条，完全符合预期结果。

方案对比 #

• 相较于DELETE ... WHERE EXISTS方案：不会出现单条匹配触发全量同值行删除的问题，匹配粒度精确到单条记录，严格遵循一对一抵消规则
• 相较于WHILE循环逐行删除方案：基于集合运算实现，数据量较大时性能远高于逐行循环，也不会因为两表记录的物理存储顺序不一致导致匹配逻辑失效

提示：如果业务要求同值记录需要按特定优先级抵消（比如最早提交的请假申请优先被撤销），只需要将两个ROW_NUMBER()函数中的ORDER BY (SELECT 1)替换为对应排序字段（如ORDER BY apply_time ASC）即可。

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