多表关联查询问题：如何获取正确的单行汇总求和结果

我拥有payments和payment_refunds两张数据表，单条支付记录可对应多条退款记录。我编写了如下SQL查询语句：

with paymentsAggr as (
select p.invoice_id, sum(p.amount) as amount, sum(pr.amount) as amount2 from payments p
        LEFT JOIN
    (SELECT 
        id, SUM(amount)
    FROM
        payments
    GROUP BY id) AS payments_total ON p.id = payments_total.id
        LEFT JOIN
    (SELECT 
        payment_id, SUM(amount) AS amount
    FROM
        payment_refunds AS pr
    GROUP BY payment_id) AS pr ON p.id = pr.payment_id
    group by p.invoice_id
)

select      
    SUM((qty * cost - cost * qty * discount_rate) * vat_rate) + SUM(cost * qty) - SUM(cost * qty * discount_rate) final,
    paymentsAggr.amount, paymentsAggr.amount2
from
invoices
        left join
    invoice_items ON invoices.id = invoice_items.invoice_id
    left join paymentsAggr on invoices.id = paymentsAggr.invoice_id
    group by paymentsAggr.invoice_id

当前查询返回多行结果，但我期望返回单行汇总数据，格式如下：

final | pAmount | prAmount
327.6     25       10

我尝试过多次求和及移除查询中的ID字段，但问题仍未解决，请求帮助修正查询以得到正确结果。

问题核心是原查询两次按invoice_id分组，导致结果被拆分为单发票维度的数据。要得到全局汇总的单行结果，需要拆分聚合逻辑，避免多表关联时的重复计算：

修正后的SQL #

WITH payments_summary AS (
    -- 计算所有支付的总金额
    SELECT SUM(amount) AS pAmount
    FROM payments
),
refunds_summary AS (
    -- 计算所有退款的总金额
    SELECT SUM(amount) AS prAmount
    FROM payment_refunds
),
invoice_total AS (
    -- 计算所有发票项的最终金额总和，简化原公式的写法
    SELECT SUM(qty * cost * (1 - discount_rate) * (1 + vat_rate)) AS final
    FROM invoices
    LEFT JOIN invoice_items ON invoices.id = invoice_items.invoice_id
)
SELECT 
    it.final,
    ps.pAmount,
    rs.prAmount
FROM invoice_total it
CROSS JOIN payments_summary ps
CROSS JOIN refunds_summary rs;

关键说明 #

1. 拆分独立聚合：将发票总金额、支付总金额、退款总金额分别在独立CTE中计算，避免多表关联产生笛卡尔积导致的重复求和。
2. 全局聚合而非分组：每个CTE都做全局求和，不按invoice_id拆分，确保得到全量汇总值。
3. 简化公式：原公式SUM((qty * cost - cost * qty * discount_rate) * vat_rate) + SUM(cost * qty) - SUM(cost * qty * discount_rate)可简化为SUM(qty * cost * (1 - discount_rate) * (1 + vat_rate))，逻辑完全一致但更简洁。
4. 移除冗余子查询：原SQL中的payments_total子查询完全多余，单条支付记录的amount本身就是对应金额，无需重复聚合。

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