多列关联两表：返回非完全匹配行并替换未匹配值的SQL方案

需求说明 #

• 基于ars、bill、codt三列关联表A和表B
• 表A中与表B**完全匹配（三列均一致）**的行，取对应B表的s4值
• 表A中非完全匹配的行（如A的codt为YPR-003或空值），用B表中codt为空的s4值（固定为zxc）填充
• 必须保留表A的所有行

原方案问题 #

• 单一LEFT JOIN仅匹配完全一致的行，非匹配行返回B表字段为NULL，无法自动填充codt为空的s4值
• INNER JOIN会丢失表A未匹配的行，不符合需求
• UNION结合两次LEFT JOIN的方案可行，但查询冗余，尤其是当A、B表本身是复杂关联结果时，会重复计算，影响性能

更优实现方案 #

方案1：双LEFT JOIN + COALESCE #

通过两次LEFT JOIN分别获取完全匹配的s4和codt为空的s4，再用COALESCE优先取完全匹配的值，无匹配时取codt为空的s4：

SELECT 
    a.ars, 
    a.bill, 
    a.codt,  -- 保留A表原codt，若需显示B表匹配的codt可调整
    a.c4, 
    COALESCE(b_full.s4, b_null.s4) AS s4
FROM A a
LEFT JOIN B b_full 
    ON a.ars = b_full.ars 
    AND a.bill = b_full.bill 
    AND a.codt = b_full.codt
LEFT JOIN B b_null 
    ON a.ars = b_null.ars 
    AND a.bill = b_null.bill 
    AND b_null.codt IS NULL;

优势：逻辑清晰，仅需两次关联，避免UNION的重复扫描，性能更优，尤其适合A、B表是复杂子查询的场景。

方案2：LATERAL JOIN（PostgreSQL适用） #

利用LATERAL JOIN动态获取优先级最高的匹配行，一次关联即可完成：

SELECT 
    a.ars, 
    a.bill, 
    a.codt, 
    a.c4, 
    b.s4
FROM A a
LEFT JOIN LATERAL (
    SELECT s4
    FROM B
    WHERE a.ars = B.ars 
      AND a.bill = B.bill 
      AND (a.codt = B.codt OR B.codt IS NULL)
    ORDER BY 
        -- 完全匹配的行优先级更高，排序后取第一行
        CASE WHEN a.codt = B.codt THEN 1 ELSE 2 END
    LIMIT 1
) b ON true;

优势：仅一次关联B表，通过排序和LIMIT确保优先取完全匹配的s4，无匹配时取codt为空的s4，代码更简洁，性能更高效。

方案3：CASE WHEN + 子查询 #

如果数据库不支持LATERAL JOIN，可使用子查询结合CASE WHEN实现：

SELECT 
    a.ars, 
    a.bill, 
    a.codt, 
    a.c4,
    CASE
        -- 先判断是否存在完全匹配的行
        WHEN EXISTS (
            SELECT 1 FROM B 
            WHERE a.ars = B.ars 
              AND a.bill = B.bill 
              AND a.codt = B.codt
        ) THEN (
            SELECT s4 FROM B 
            WHERE a.ars = B.ars 
              AND a.bill = B.bill 
              AND a.codt = B.codt
        )
        -- 否则取codt为空的s4
        ELSE (
            SELECT s4 FROM B 
            WHERE a.ars = B.ars 
              AND a.bill = B.bill 
              AND B.codt IS NULL
        )
    END AS s4
FROM A a;

注意：该方案需确保B表中对于同一ars+bill组合，完全匹配的行最多1条，codt为空的行最多1条，避免子查询返回多行报错。

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