Azure SQL DB中获取含重复项的两表差集并存入第三表的SQL实现

核心思路 #

普通EXCEPT会自动去重，要保留原本的重复行数，需要对两张表中完全相同的行按出现顺序分配独立序号，再将序号作为匹配条件之一做差集，即可保留原始重复行数。

1. 查询A表减B表的结果（并存入第三张表） #

WITH A_Ranked AS (
    SELECT 
        AppName,
        [Creation Time],
        [Completion Time],
        Status,
        -- 按所有列分组，相同内容的行按出现顺序编号
        ROW_NUMBER() OVER (
            PARTITION BY AppName, [Creation Time], [Completion Time], Status 
            ORDER BY (SELECT 0)
        ) AS RowNum
    FROM TableA
),
B_Ranked AS (
    SELECT 
        AppName,
        [Creation Time],
        [Completion Time],
        Status,
        ROW_NUMBER() OVER (
            PARTITION BY AppName, [Creation Time], [Completion Time], Status 
            ORDER BY (SELECT 0)
        ) AS RowNum
    FROM TableB
)
-- 结果直接存入第三张表TableA_Minus_B，不需要存表则删除INTO子句即可
SELECT AppName, [Creation Time], [Completion Time], Status
INTO TableA_Minus_B
FROM A_Ranked
EXCEPT
SELECT AppName, [Creation Time], [Completion Time], Status
FROM B_Ranked

2. 查询B表减A表的结果（并存入第三张表） #

WITH A_Ranked AS (
    SELECT 
        AppName,
        [Creation Time],
        [Completion Time],
        Status,
        ROW_NUMBER() OVER (
            PARTITION BY AppName, [Creation Time], [Completion Time], Status 
            ORDER BY (SELECT 0)
        ) AS RowNum
    FROM TableA
),
B_Ranked AS (
    SELECT 
        AppName,
        [Creation Time],
        [Completion Time],
        Status,
        ROW_NUMBER() OVER (
            PARTITION BY AppName, [Creation Time], [Completion Time], Status 
            ORDER BY (SELECT 0)
        ) AS RowNum
    FROM TableB
)
-- 结果直接存入第三张表TableB_Minus_A，不需要存表则删除INTO子句即可
SELECT AppName, [Creation Time], [Completion Time], Status
INTO TableB_Minus_A
FROM B_Ranked
EXCEPT
SELECT AppName, [Creation Time], [Completion Time], Status
FROM A_Ranked

说明 #

• 纯SQL实现，无需存储过程，完全兼容Azure SQL数据库
• ORDER BY (SELECT 0)是为了满足窗口函数的语法要求，不需要指定特定排序规则，只要相同内容的行按顺序编号即可，不影响最终结果
• 该逻辑会自动匹配行内容和出现次数：比如A中某行出现2次、B中同内容行出现2次，会完全抵消；A中某行出现2次、B中同内容行出现0次，会保留2行在A减B的结果中，完全符合需求预期

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