多表连接求和异常：如何正确计算发票的项总计、付款总计与未结余额

直接关联invoices、items和payments三张表时，由于单张发票可能对应多条商品记录和多条付款记录，连接后会产生笛卡尔积，导致SUM(payments.total)和SUM(items.total)被重复计算，结果失真。

最优表连接实现方式 #

先分别对items和payments按invoice_id做聚合求和，得到每个发票的商品总金额和付款总金额，再将这两个聚合结果与invoices表左连接，彻底避免重复统计问题，同时写法简洁、性能更优：

SELECT
    inv.id,
    inv.number,
    COALESCE(it.item_total, 0) AS item_total,
    COALESCE(py.payment_total, 0) AS payment_total,
    COALESCE(it.item_total, 0) - COALESCE(py.payment_total, 0) AS outstanding_balance
FROM invoices inv
LEFT JOIN (
    SELECT invoice_id, SUM(total) AS item_total
    FROM items
    GROUP BY invoice_id
) it ON it.invoice_id = inv.id
LEFT JOIN (
    SELECT invoice_id, SUM(total) AS payment_total
    FROM payments
    GROUP BY invoice_id
) py ON py.invoice_id = inv.id

关键说明 #

• 消除笛卡尔积：通过预聚合子查询，确保每个invoice_id只对应一条汇总记录，从根源上避免了多对多连接产生的重复数据。
• 处理空值：使用COALESCE函数将无商品/无付款的发票金额置为0，避免NULL值导致计算结果异常。
• 性能优势：相比嵌套相关子查询，预聚合仅需对items和payments各做一次全表扫描（若invoice_id有索引则效率更高），连接时数据量大幅减少，执行效率远高于每条发票触发两次子查询的方式。

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