JOIN操作中SUM字段重复计算问题求助

问题核心：当单条invoice关联多条payment_details记录时，left join会将该invoice的记录复制多份（每条payment_details对应一行），导致SUM(invoices.amount)重复累加同一invoice的金额，结果出错。

方案一：先聚合payment_details再关联主查询 #

通过子查询预先按invoice计算总支付金额，确保每个invoice仅对应一行数据，避免关联后产生重复行：

select
    `sites`.`number`,
    SUM(invoices.amount) as total_invoice_amount,
    COALESCE(payment_totals.total_payment, 0) as pda,
    COUNT(DISTINCT warrels.id) as warrels
from `sites`
left join `invoices`
    on `sites`.`id` = `invoices`.`invoiceable_id`
    and `invoices`.`invoiceable_type` = 'App\Models\Site' -- 将条件移至join子句，保留无invoice的site（按需选择）
left join `warrels`
    on `sites`.`id` = `warrels`.`site_id`
left join (
    -- 子查询：按invoice分组计算支付总额
    select `payable_id`, SUM(amount) as total_payment
    from `payment_details`
    group by `payable_id`
) as payment_totals
    on `invoices`.`id` = payment_totals.payable_id
where `sites`.`number` LIKE 'AU22%'
group by `sites`.`number`
order by `sites`.`number` asc

方案二：先聚合站点级数据再关联支付总额 #

先对site、invoice、warrels进行聚合，得到正确的发票总额和warrels数量，再关联按站点聚合的支付总额，彻底避免多表关联的笛卡尔积问题：

select
    site_summary.number,
    site_summary.total_invoice_amount,
    COALESCE(payment_summary.total_payment, 0) as pda,
    site_summary.warrels_count
from (
    -- 子查询：按站点聚合发票总额和warrels数量
    select
        s.id,
        s.number,
        SUM(i.amount) as total_invoice_amount,
        COUNT(w.id) as warrels_count
    from sites s
    left join invoices i on s.id = i.invoiceable_id and i.invoiceable_type = 'App\Models\Site'
    left join warrels w on s.id = w.site_id
    where s.number LIKE 'AU22%'
    group by s.id, s.number
) as site_summary
left join (
    -- 子查询：按站点聚合支付总额
    select
        i.invoiceable_id,
        SUM(pd.amount) as total_payment
    from invoices i
    join payment_details pd on i.id = pd.payable_id
    where i.invoiceable_type = 'App\Models\Site'
    group by i.invoiceable_id
) as payment_summary
    on site_summary.id = payment_summary.invoiceable_id
order by site_summary.number asc

注意事项 #

• 原查询中where子句的invoices.invoiceable_type = 'App\Models\Site'会过滤掉无发票的站点，若需保留这类站点，需将该条件移至left join的on子句中（如方案一所示）。
• 使用COALESCE函数确保无支付记录的站点返回0而非NULL，结果更符合预期。

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