SQL关联映射表优先取匹配项无匹配取标准记录问题排查

原查询存在的问题 #

你当前的JOIN逻辑没有做优先级过滤，会把所有满足匹配规则的记录全部返回：比如Pr1的Col2=C1、Col3=TypeA，既可以匹配到LookupCol2=C1的精确映射记录，也能匹配到LookupCol2为空的默认映射记录，导致同一条Table1记录对应同一个Col4返回两条重复数据，不符合你要求的「优先取精确匹配」的规则。

修正方案 #

可以用窗口函数给映射匹配结果按优先级排序，只取每组优先级最高的记录即可，修改后的SQL如下：

WITH match_result AS (
    SELECT 
        a.Col1,
        a.Col2,
        a.Col3,
        b.Col4,
        b.Col5,
        -- 按优先级排序：精确匹配优先级更高，值越小排名越靠前
        ROW_NUMBER() OVER (
            PARTITION BY a.Col1, b.Col4 
            ORDER BY CASE WHEN b.LookupCol2 IS NOT NULL THEN 1 ELSE 2 END ASC
        ) AS rn
    FROM Table1 a
    INNER JOIN MappingTable b 
        ON a.Col3 = b.LookupCol3
        -- 原CASE逻辑可以简化为该条件，可读性更高
        AND (b.LookupCol2 IS NULL OR a.Col2 = b.LookupCol2)
)
SELECT Col1, Col2, Col3, Col4, Col5
FROM match_result
WHERE rn = 1;

逻辑说明 #

1. 先查询出所有符合匹配规则的结果，包含精确匹配和默认匹配两类
2. 按Table1的单条记录（用Col1唯一标识）和映射表的Col4分组，给每组内的记录排序：LookupCol2非空的精确匹配排第1位，默认匹配排第2位
3. 最后只取每组排序第1的记录，刚好符合「优先取精确匹配，无精确匹配时取默认记录」的需求

注：你提供的测试插入SQL里少了Pr5的插入语句，补上INSERT INTO Table1 VALUES ('Pr5','C5','TypeA')即可得到和预期完全一致的结果。

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