MySQL LEFT JOIN关联付款退款产生重复记录的问题与解决方案

问题本质 #

你遇到的是SQL多维度关联产生的笛卡尔积重复问题：当单张发票同时关联N条付款记录、M条退款记录时，直接左连接会生成N*M条结果，每条退款会和所有付款匹配，导致金额被重复计算。

场景1：需要付款/退款分开的明细行 #

实现你要的每行仅展示一笔付款/退款、另一列留空的效果，用UNION ALL拆分两类数据分别查询即可：

SELECT 
    invoice.invoice_id,
    NULL AS refund_amount,
    payment.amount AS payment_amount
FROM invoice
LEFT JOIN payment_invoice ON payment_invoice.invoice_id = invoice.invoice_id
LEFT JOIN payment ON payment.payment_id = payment_invoice.payment_id
WHERE invoice.company_id = 59 AND invoice.reservation_id = 1157
AND payment.amount IS NOT NULL

UNION ALL

SELECT 
    invoice.invoice_id,
    refund.amount AS refund_amount,
    NULL AS payment_amount
FROM invoice
LEFT JOIN refund_invoice ON refund_invoice.invoice_id = invoice.invoice_id
LEFT JOIN refund ON refund.refund_id = refund_invoice.refund_id
WHERE invoice.company_id = 59 AND invoice.reservation_id = 1157
AND refund.amount IS NOT NULL

场景2：按发票维度汇总的最终统计结果 #

不需要使用临时表，将付款、退款数据各自按发票ID预聚合之后再关联，即可避免交叉重复，优化后的查询语句如下：

SELECT
    inv.invoice_id,
    inv.company_id,
    istatus.invoice_status,
    inv.total AS invoice_total_amount,
    IFNULL(res.reservation_id, 'ADHOC INVOICE') AS reservation_id,
    IFNULL(r.total_refund, 0) AS refund_amount,
    IFNULL(p.total_payment, 0) AS payment_amount,
    inv.payment_processor_fee,
    inv.vquip_fee,
    IFNULL(p.total_payment, 0) - IFNULL(r.total_refund, 0) AS gross_amount_to_be_dispersed,
    IFNULL(p.total_payment, 0) - IFNULL(r.total_refund, 0)
        - IFNULL(inv.vquip_fee, 0)
        - IFNULL(CASE WHEN inv.charge_customers_payment_processing_fee = 0 THEN inv.payment_processor_fee ELSE 0 END, 0) AS amount_to_be_dispersed,
    inv.charge_customers_payment_processing_fee
FROM invoice inv
INNER JOIN invoice_status istatus ON istatus.invoice_status_id = inv.status_id
LEFT JOIN reservation res ON res.reservation_id = inv.reservation_id
LEFT JOIN reservation_status rstatus ON rstatus.reservation_status_id = res.reservation_status_id
-- 关联预聚合后的付款总额
LEFT JOIN (
    SELECT pi.invoice_id, SUM(p.amount) AS total_payment
    FROM payment_invoice pi
    INNER JOIN payment p ON p.payment_id = pi.payment_id
    WHERE p.processor_payment_id IS NOT NULL
    GROUP BY pi.invoice_id
) p ON p.invoice_id = inv.invoice_id
-- 关联预聚合后的退款总额
LEFT JOIN (
    SELECT ri.invoice_id, SUM(r.amount) AS total_refund
    FROM refund_invoice ri
    INNER JOIN refund r ON r.refund_id = ri.refund_id
    WHERE r.processor_refund_id IS NOT NULL
    GROUP BY ri.invoice_id
) r ON r.invoice_id = inv.invoice_id
WHERE
    istatus.invoice_status IN ('Paid', 'Partially Refunded')
    AND inv.has_been_dispersed = 0
    AND rstatus.reservation_status IN ('Closed', 'Ended', 'Cancelled')
    AND (p.total_payment IS NOT NULL OR r.total_refund IS NOT NULL)

内容的提问来源于stack exchange，提问作者jtmg.io