MySQL查询未全额支付订单：计算发票总额、付款总额及余额

你的SQL出现数值偏大的原因是：直接将Order_summary同时左连Invoices和Payments会产生笛卡尔积。比如某订单有2条发票记录、3条付款记录，关联后会生成2×3=6条重复组合的记录，求和时会把发票金额重复计算3次、付款金额重复计算2次，最终导致结果异常。

正确的做法是先分别对Invoices和Payments按order_ref聚合计算总额，再和Order_summary关联，避免重复计算：

SELECT 
    os.*,
    COALESCE(it.invoice_total, 0) AS invoice_total,
    COALESCE(pt.payment_total, 0) AS payment_total,
    COALESCE(it.invoice_total, 0) - COALESCE(pt.payment_total, 0) AS balance
FROM Order_summary os
LEFT JOIN (
    SELECT 
        order_ref,
        SUM(rate * qty) AS invoice_total
    FROM Invoices
    GROUP BY order_ref
) it ON it.order_ref = os.order_ref
LEFT JOIN (
    SELECT 
        order_ref,
        SUM(Amount) AS payment_total
    FROM Payments
    GROUP BY order_ref
) pt ON pt.order_ref = os.order_ref
WHERE COALESCE(it.invoice_total, 0) - COALESCE(pt.payment_total, 0) > 0

代码说明 #

• 子查询it：先按订单号聚合计算每个订单的发票总金额，确保每个订单只返回一条总额记录
• 子查询pt：同理，先按订单号聚合计算每个订单的付款总金额
• 最后将两个聚合后的结果与主表关联，再过滤出余额大于0的未全额支付订单

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