DB2 SQL中如何设置LEFT JOIN连接条件的匹配优先级

问题原因 #

直接用OR拼接ON后的连接条件时，只要B表记录满足任意一个条件分支就会和A表记录关联，不会做优先级筛选，因此同一条A表记录匹配到多条B表记录时会全部返回，产生重复行。

实现思路 #

利用DB2支持的ROW_NUMBER()窗口函数，先给B表记录按照定义的匹配优先级排序，每个name分组下只保留优先级最高的1条记录再做关联，即可保证每条A表记录仅关联到最高优先级的单条B表记录。
优先级规则对应排序权重：

• 规则1（B.FLAG='NA' AND B.SITE='NA'）：权重1，最高优先级
• 规则2（B.FLAG='CITY'）：权重2，次优先级
• 规则3（B.FLAG='COUNTRY'）：权重3，最低优先级

可直接运行的SQL代码 #

SELECT A.name, B.flag, B.site
FROM A
LEFT JOIN (
    SELECT
        *,
        ROW_NUMBER() OVER(
            PARTITION BY name
            ORDER BY CASE
                WHEN FLAG = 'NA' AND SITE = 'NA' THEN 1
                WHEN FLAG = 'CITY' THEN 2
                WHEN FLAG = 'COUNTRY' THEN 3
                ELSE 999
            END
        ) AS match_rn
    FROM B
) B
ON A.name = B.name
AND match_rn = 1
AND (
    (B.FLAG = 'NA' AND B.SITE = 'NA')
    OR (B.FLAG = 'CITY' AND B.SITE = A.city)
    OR (B.FLAG = 'COUNTRY' AND B.SITE = A.country)
)

结果验证 #

执行上述SQL后返回结果和预期完全一致：

注意事项 #

• 代码中NA、CITY、COUNTRY作为字符串常量已补充单引号，避免DB2将其识别为列名抛出语法错误
• 如果同一优先级下存在多条匹配记录，可在窗口函数的ORDER BY后追加排序字段，保证返回结果的确定性

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