求获取匹配与不匹配记录的SQL查询语句（附表结构及预期结果）

需求 #

编写SQL查询语句，获取Table1中每条记录与Table2的匹配或不匹配状态。

表结构 #

Table1 #

Table2 #

预期结果 #

当前尝试的查询语句 #

SELECT 
    T1.Col1,
    CASE
        WHEN T2.Col1 IS NOT NULL THEN 'Matched'
        ELSE 'NotMatched'
    END AS Col2
FROM
    Table1 T1
LEFT JOIN 
    Table2 T2 ON T1.Col1 = T2.Col1;

该语句无法得到预期结果，因为左连接会让Table1中每个匹配Col1的行都关联到Table2的对应行，导致同一Col1的所有行都被标记为Matched，不符合需求中仅第一条匹配、其余不匹配的逻辑。

正确的SQL查询语句 #

WITH RankedTable1 AS (
    SELECT 
        Col1,
        ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY (SELECT NULL)) AS rn
    FROM Table1
)
SELECT 
    rt1.Col1,
    CASE 
        WHEN rt1.rn <= (SELECT COUNT(*) FROM Table2 t2 WHERE t2.Col1 = rt1.Col1) THEN 'Matched'
        ELSE 'Not Matched'
    END AS Col2
FROM RankedTable1 rt1;

逻辑说明 #

1. 用ROW_NUMBER()窗口函数给Table1中同一Col1的记录按原始顺序编号（PARTITION BY Col1分组，ORDER BY (SELECT NULL)保留原始行顺序）。
2. 针对每条编号后的记录，判断其行号是否小于等于该Col1在Table2中的出现次数：
   • 由于当前Table2中每个Col1仅出现1次，所以行号为1的标记为Matched，其余为Not Matched
   • 若Table2中同一Col1存在多条记录，该语句会自动适配，让对应数量的行标记为Matched

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