如何在MSSQL与PostgreSQL中实现带英文兜底的双主键表连接

拥有两张表NAME和ADDRESS，两者的主键均为ID与Language。需要基于ID和Language进行连接，优先返回匹配Language的条目；当ADDRESS表中无对应ID的匹配Language条目时，返回同ID且Language为ENG的条目。同时方案需适配MSSQL和PostgreSQL。

示例数据 #

NAME表 #

ADDRESS表 #

当前尝试的问题 #

• 内连接只能返回完全匹配的条目，丢失了Zark这条记录
• 左连接会让Zark对应的Addr为null，不符合需求

期望结果 #

可以通过左连接+优先级排序+过滤的方式实现，核心思路是先为每个NAME记录匹配所有可能的ADDRESS条目（同ID的匹配Language或ENG），然后为每个条目标记优先级，最后保留优先级最高的那条记录。

通用SQL语句 #

WITH ranked_addresses AS (
    SELECT 
        n.Name,
        a.Addr,
        -- 标记优先级：完全匹配Language的优先级为1，ENG兜底的为2
        ROW_NUMBER() OVER (
            PARTITION BY n.ID, n.Language 
            ORDER BY CASE WHEN a.Language = n.Language THEN 1 ELSE 2 END
        ) AS rn
    FROM NAME n
    LEFT JOIN ADDRESS a 
        ON n.ID = a.ID 
        AND a.Language IN (n.Language, 'ENG')
)
SELECT Name, Addr
FROM ranked_addresses
WHERE rn = 1;

逻辑解释 #

1. CTE表ranked_addresses：
   • 左连接NAME和ADDRESS，连接条件放宽为同ID且ADDRESS的Language是当前NAME的Language或者ENG
   • 使用ROW_NUMBER()窗口函数，按ID和Language分组，为每个分组内的条目排序：完全匹配的条目排第1，ENG兜底的排第2
2. 最终查询：筛选出每个分组中rn=1的记录，即优先级最高的条目

验证结果 #

执行上述SQL后，会得到期望的结果：

兼容性说明 #

• ROW_NUMBER()窗口函数和CTE语法在MSSQL和PostgreSQL中均支持，无需修改即可直接使用
• 若存在同ID下既无匹配Language也无ENG条目的情况，Addr会返回null，可根据需求添加COALESCE处理

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