如何关联两张表并标记匹配/未匹配记录（含重复值处理）

现有查询的问题 #

你当前的查询用LEFT JOIN仅保留了Table1的所有记录，导致Table2中独有的30没出现在结果里，无法完全满足预期需求。

修正后的查询语句 #

WITH cte1 AS (
    SELECT Col1, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) rn
    FROM Table1
),
cte2 AS (
    SELECT Col1, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) rn
    FROM Table2
)
SELECT 
    COALESCE(T1.Col1, T2.Col1) AS Col1,
    CASE 
        WHEN T1.Col1 IS NOT NULL AND T2.Col1 IS NOT NULL THEN 'Matched'
        ELSE 'Not Matched'
    END AS Col2
FROM cte1 T1
FULL OUTER JOIN cte2 T2
    ON T1.Col1 = T2.Col1 AND T1.rn = T2.rn
ORDER BY Col1;

代码说明 #

1. 给重复值加行号：分别对Table1和Table2中相同Col1的记录添加行号，确保同一值的多条记录能按顺序一对一匹配。
2. 全外连接保留所有记录：用FULL OUTER JOIN同时保留两个表的所有数据，既包含Table1独有的50，也包含Table2独有的30。
3. 处理空值的Col1：用COALESCE函数取两侧非空的Col1值，保证结果中Col1始终有有效值。
4. 标记匹配状态：当两侧的Col1都不为空时，说明是行号匹配的记录，标记为Matched；否则标记为Not Matched。
5. 排序保证结果顺序：按Col1排序，让输出顺序和预期一致。

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