Left Join关联多表求和时重复数据致结果错误问题

问题核心：同时关联一对多的发票项表和付款表时，会产生笛卡尔积，导致发票行被重复展开，最终求和结果错误（付款总和被多次累加）。

原因分析 #

你之前的查询中，先关联了invoices和invoice_items（每个发票对应多个商品项），再关联聚合后的付款表。此时每个商品项行都会携带同一个发票的付款总和，执行SUM(p_sum.paymentSum)时，相当于把该发票的付款总和重复加了N次（N为商品项数量），导致付款结果偏大。

正确解决方案 #

先分别对发票项和付款按发票ID做聚合，得到每个发票的独立金额与付款总和，再将这两个聚合结果与发票表关联，彻底避免笛卡尔积：

WITH invoice_calculations AS (
    SELECT 
        inv.id AS invoice_id,
        -- 简化计算逻辑：先算折扣后金额，再加增值税，与原查询逻辑一致
        SUM(qty * cost * (1 - inv.discount_rate) * (1 + vat_rate)) AS final_amount
    FROM invoices inv
    JOIN invoice_items inv_items ON inv.id = inv_items.invoice_id
    GROUP BY inv.id
),
payment_summaries AS (
    SELECT 
        invoice_id,
        SUM(amount) AS total_payments
    FROM payments
    GROUP BY invoice_id
)
SELECT 
    inv.id,
    -- 处理无商品项的发票，默认金额为0
    COALESCE(inv_calc.final_amount, 0) AS final,
    -- 处理无付款的发票，默认付款为0
    COALESCE(pay_sum.total_payments, 0) AS payments_sum
FROM invoices inv
LEFT JOIN invoice_calculations inv_calc ON inv.id = inv_calc.invoice_id
LEFT JOIN payment_summaries pay_sum ON inv.id = pay_sum.invoice_id;

关键说明 #

1. invoice_calculations CTE：单独计算每个发票的最终金额，避免和付款表关联时产生行重复；计算公式做了简化，逻辑等价于你原查询的复杂表达式。
2. payment_summaries CTE：提前按发票ID聚合付款总和，确保每个发票只有一行付款数据。
3. 最终关联：用LEFT JOIN保证所有发票都能被查询到，COALESCE处理无商品项或无付款的边界情况。

原查询的快速修正（非最优解） #

如果要在你原查询基础上修改，只需要去掉付款总和的外层SUM()，改用MAX()或直接取值（因为同一发票的所有行对应的paymentSum是相同的）：

WITH paymentSumAggr AS (
    SELECT invoice_id, SUM(amount) AS paymentSum FROM payments GROUP BY invoice_id
)
SELECT      
    invoices.id,
    SUM((qty * cost - cost * qty * discount_rate) * vat_rate) + SUM(cost * qty) - SUM(cost * qty * discount_rate) final,
    MAX(p_sum.paymentSum) AS payments_sum -- 替换SUM为MAX，或直接用p_sum.paymentSum
FROM invoices
LEFT JOIN invoice_items ON invoices.id = invoice_items.invoice_id
LEFT JOIN paymentSumAggr p_sum ON invoices.id = p_sum.invoice_id
GROUP BY invoices.id, p_sum.paymentSum; -- 分组需包含paymentSum

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