MySQL LEFT JOIN 导致付款与退款记录重复

结论

问题不在 LEFT JOIN，而在于查询同时连接了两个一对多关系：

• 一张 invoice 对应多条 payment
• 同一张 invoice 也对应多条 refund

假设某张 invoice 有 2 条 payment 和 2 条 refund，连接后会产生 2 × 2 = 4 行。此时直接执行 SUM(payment.amount) 和 SUM(refund.amount)，会重复累计每笔金额。

解决方法是分别按 invoice_id 汇总 payment 和 refund，确保两个汇总结果都是“一张 invoice 一行”，然后再将它们连接到 invoice。

推荐写法：先聚合，再 JOIN

MySQL 8.0 可以使用 CTE：

WITH payment_totals AS (
    SELECT
        pi.invoice_id,
        SUM(p.amount) AS payment_amount
    FROM payment_invoice AS pi
    INNER JOIN payment AS p
        ON p.payment_id = pi.payment_id
    WHERE p.processor_payment_id IS NOT NULL
    GROUP BY pi.invoice_id
),
refund_totals AS (
    SELECT
        ri.invoice_id,
        SUM(r.amount) AS refund_amount
    FROM refund_invoice AS ri
    INNER JOIN refund AS r
        ON r.refund_id = ri.refund_id
    WHERE r.processor_refund_id IS NOT NULL
    GROUP BY ri.invoice_id
)
SELECT
    i.invoice_id,
    i.company_id,
    ist.invoice_status,
    i.total AS invoice_total_amount,
    COALESCE(
        CAST(res.reservation_id AS CHAR),
        'ADHOC INVOICE'
    ) AS reservation_id,
    COALESCE(rt.refund_amount, 0) AS refund_amount,
    COALESCE(pt.payment_amount, 0) AS payment_amount,
    i.payment_processor_fee,
    i.vquip_fee,
    COALESCE(pt.payment_amount, 0)
        - COALESCE(rt.refund_amount, 0)
        AS gross_amount_to_be_dispersed,
    COALESCE(pt.payment_amount, 0)
        - COALESCE(rt.refund_amount, 0)
        - COALESCE(i.vquip_fee, 0)
        - CASE
            WHEN i.charge_customers_payment_processing_fee = 0
                THEN COALESCE(i.payment_processor_fee, 0)
            ELSE 0
          END
        AS amount_to_be_dispersed,
    i.charge_customers_payment_processing_fee
FROM invoice AS i
INNER JOIN invoice_status AS ist
    ON ist.invoice_status_id = i.status_id
LEFT JOIN reservation AS res
    ON res.reservation_id = i.reservation_id
LEFT JOIN reservation_status AS rst
    ON rst.reservation_status_id = res.reservation_status_id
LEFT JOIN payment_totals AS pt
    ON pt.invoice_id = i.invoice_id
LEFT JOIN refund_totals AS rt
    ON rt.invoice_id = i.invoice_id
WHERE ist.invoice_status IN ('Paid', 'Partially Refunded')
  AND i.has_been_dispersed = 0
  AND rst.reservation_status IN ('Closed', 'Ended', 'Cancelled')
  AND (
      pt.invoice_id IS NOT NULL
      OR rt.invoice_id IS NOT NULL
  );

payment_totals 和 refund_totals 最多只会为每张 invoice 返回一行，所以外层查询不会产生 payment 与 refund 的交叉组合，也不再需要 GROUP BY。

MySQL 5.7 及更早版本

不支持 CTE 时，可以改用派生表，处理逻辑相同：

SELECT
    i.invoice_id,
    i.company_id,
    ist.invoice_status,
    i.total AS invoice_total_amount,
    COALESCE(
        CAST(res.reservation_id AS CHAR),
        'ADHOC INVOICE'
    ) AS reservation_id,
    COALESCE(rt.refund_amount, 0) AS refund_amount,
    COALESCE(pt.payment_amount, 0) AS payment_amount,
    i.payment_processor_fee,
    i.vquip_fee,
    COALESCE(pt.payment_amount, 0)
        - COALESCE(rt.refund_amount, 0)
        AS gross_amount_to_be_dispersed,
    COALESCE(pt.payment_amount, 0)
        - COALESCE(rt.refund_amount, 0)
        - COALESCE(i.vquip_fee, 0)
        - CASE
            WHEN i.charge_customers_payment_processing_fee = 0
                THEN COALESCE(i.payment_processor_fee, 0)
            ELSE 0
          END
        AS amount_to_be_dispersed,
    i.charge_customers_payment_processing_fee
FROM invoice AS i
INNER JOIN invoice_status AS ist
    ON ist.invoice_status_id = i.status_id
LEFT JOIN reservation AS res
    ON res.reservation_id = i.reservation_id
LEFT JOIN reservation_status AS rst
    ON rst.reservation_status_id = res.reservation_status_id
LEFT JOIN (
    SELECT
        pi.invoice_id,
        SUM(p.amount) AS payment_amount
    FROM payment_invoice AS pi
    INNER JOIN payment AS p
        ON p.payment_id = pi.payment_id
    WHERE p.processor_payment_id IS NOT NULL
    GROUP BY pi.invoice_id
) AS pt
    ON pt.invoice_id = i.invoice_id
LEFT JOIN (
    SELECT
        ri.invoice_id,
        SUM(r.amount) AS refund_amount
    FROM refund_invoice AS ri
    INNER JOIN refund AS r
        ON r.refund_id = ri.refund_id
    WHERE r.processor_refund_id IS NOT NULL
    GROUP BY ri.invoice_id
) AS rt
    ON rt.invoice_id = i.invoice_id
WHERE ist.invoice_status IN ('Paid', 'Partially Refunded')
  AND i.has_been_dispersed = 0
  AND rst.reservation_status IN ('Closed', 'Ended', 'Cancelled')
  AND (
      pt.invoice_id IS NOT NULL
      OR rt.invoice_id IS NOT NULL
  );

这里通常不需要创建临时表。派生表或 CTE 可以在一条查询中完成同样的工作，还能省去临时表的命名和清理问题。

如果确实需要 payment 和 refund 分别占一行

汇总时不需要把两类记录拆成独立行。如果要展示明细，可以使用 UNION ALL：

SELECT
    pi.invoice_id,
    NULL AS refund_amount,
    p.amount AS payment_amount
FROM payment_invoice AS pi
INNER JOIN payment AS p
    ON p.payment_id = pi.payment_id

UNION ALL

SELECT
    ri.invoice_id,
    r.amount AS refund_amount,
    NULL AS payment_amount
FROM refund_invoice AS ri
INNER JOIN refund AS r
    ON r.refund_id = ri.refund_id;

这样 payment 与 refund 不会互相配对。如果只是计算每张 invoice 的总额，仍应优先分别聚合，再进行连接。

注意事项

不要使用 SUM(DISTINCT amount) 修复重复

不同 payment 可能具有相同金额。例如，两笔付款都是 100.00，SUM(DISTINCT amount) 只会计算一次，结果仍然不对。去重应依据记录关系，而不是金额。

invoice 自身字段不应再次 SUM

预聚合后，外层查询中的每张 invoice 只有一行，因此没有必要这样写：

SUM(invoice.vquip_fee)

直接使用下面的写法即可：

COALESCE(i.vquip_fee, 0)

如果以后在外层增加其他一对多连接，使用 SUM 还可能导致 invoice 级费用被再次重复累计。

检查关联表是否存在重复关系

即使已经预聚合，只要 payment_invoice 中存在重复的 (invoice_id, payment_id)，对应的 payment 仍会被重复计算。可以根据业务规则添加唯一约束：

ALTER TABLE payment_invoice
ADD UNIQUE KEY uq_payment_invoice (invoice_id, payment_id);

ALTER TABLE refund_invoice
ADD UNIQUE KEY uq_refund_invoice (invoice_id, refund_id);

执行前，需要先确认现有数据中没有重复关系。

LEFT JOIN 后的 WHERE 条件可能排除空记录

当前条件是：

AND rst.reservation_status IN ('Closed', 'Ended', 'Cancelled')

没有 reservation 的 invoice 会被排除，因为此时 rst.reservation_status 为 NULL。在这个条件下，相关的 LEFT JOIN 实际效果接近 INNER JOIN。

如果还要保留 ADHOC invoice，可以根据业务要求改成：

AND (
    rst.reservation_status IN ('Closed', 'Ended', 'Cancelled')
    OR i.reservation_id IS NULL
)

使用 OR 代替 ||

MySQL 默认支持用 || 表示逻辑 OR，但启用 PIPES_AS_CONCAT SQL mode 后，|| 会被解释为字符串连接。为避免不同环境产生不同结果，应明确写成：

pt.invoice_id IS NOT NULL OR rt.invoice_id IS NOT NULL

多个一对多表参与汇总时，应先将每个明细表聚合到主表粒度，再执行 JOIN。这样可以避免组合膨胀，并让 payment、refund 和 invoice 级费用各自独立计算。

备注：内容仅供参考。