求助：优化基于关联查询实现PO与发票数据匹配的SQL语句

需求说明 #

从PO表出发，通过Reference1、Reference2表关联Invoice表，获取对应PO的发票编号和金额：

• 优先用Reference1匹配PO编号，匹配不到则尝试Reference2
• 若两个Reference表都无法匹配，发票金额显示0.00，发票编号留空

表结构及数据 #

PO表 #

Vendor         PONum     POAmt
ABC Company    10001      1,250.00
DEF Company    10002      3,254.00
GHI Company    10003        854.00
KLM Company    10004      1,572.00
NOP Company    10005      6,000.00

Reference1表 #

PONum   RefNum
10001   1
10002   2

Reference2表 #

PONum   RefNum
10003   3
10004   4

Invoice表 #

RefNum   InvNum   InvAmt
1        I0001    1,250.00
2        I0002    3,254.00
3        I0003      854.00
4        I0004    1,572.00

期望结果 #

Vendor         PONum     POAmt      InvNum   InvAmt
ABC Company    10001      1,250.00   I0001    1,250.00
DEF Company    10002      3,254.00   I0002    3,254.00
GHI Company    10003        854.00   I0003      854.00
KLM Company    10004      1,572.00   I0004    1,572.00
NOP Company    10005      6,000.00            0.00

现有SQL问题 #

现有SQL存在两个核心错误：

1. 关联字段错误：PO表没有RefNum字段，却用t1.RefNum关联Reference表，实际应该用t1.PONum = t2.PONum
2. 使用UNION ALL会导致重复数据（部分PO会在两个子查询中都返回结果），不符合"优先匹配Reference1"的逻辑

SELECT *
FROM (
    SELECT t1.Vendor,t1.PONum, t1.POAmt, t3.InvNum, t3.InvAmt  
    FROM PO t1
    LEFT JOIN Reference1 t2 ON t2.PONum = t1.RefNum  -- 错误：PO表无RefNum字段
    LEFT JOIN Invoice t3 ON t3.RefNum = t2.RefNum
    UNION ALL
    SELECT t1.Vendor,t1.PONum, t1.POAmt, t3.InvNum, t3.InvAmt  
    FROM PO t1
    LEFT JOIN Reference2 t2 ON t2.PONum = t1.RefNum  -- 同样的字段错误
    LEFT JOIN Invoice t3 ON t3.RefNum = t2.RefNum
) x

修正后的SQL方案 #

方案1：合并Reference表后关联 #

先将Reference1和Reference2合并成一个完整的PO-Ref映射表，再左关联PO和Invoice，同时处理无匹配的情况：

SELECT 
    t1.Vendor,
    t1.PONum,
    t1.POAmt,
    t3.InvNum,
    COALESCE(t3.InvAmt, '0.00') AS InvAmt
FROM PO t1
LEFT JOIN (
    SELECT PONum, RefNum FROM Reference1
    UNION
    SELECT PONum, RefNum FROM Reference2
) t2 ON t1.PONum = t2.PONum
LEFT JOIN Invoice t3 ON t2.RefNum = t3.RefNum
ORDER BY t1.PONum;

方案2：优先匹配Reference1，再 fallback 到Reference2 #

通过两次左关联Reference表，用COALESCE优先取Reference1的匹配结果，再取Reference2的，最后关联Invoice：

SELECT 
    t1.Vendor,
    t1.PONum,
    t1.POAmt,
    t4.InvNum,
    COALESCE(t4.InvAmt, '0.00') AS InvAmt
FROM PO t1
LEFT JOIN Reference1 t2 ON t1.PONum = t2.PONum
LEFT JOIN Reference2 t3 ON t1.PONum = t3.PONum
LEFT JOIN Invoice t4 ON COALESCE(t2.RefNum, t3.RefNum) = t4.RefNum
ORDER BY t1.PONum;

修正说明 #

1. 修复了关联字段错误，改用PO.PONum关联Reference表的PONum
2. 避免了UNION ALL导致的重复数据，确保每个PO只返回一条结果
3. 使用COALESCE函数处理无匹配的场景，将发票金额默认设为0.00
4. 按PONum排序，与期望结果顺序一致

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