MySQL 如何从 invoices 中减去 payments

2026-10-24支付小周

结论

先按 ClientId 分别汇总 Invoices 和 Payments，再将两组汇总结果连接到 Clients。如果先连接两张明细表再执行 SUM()，一对多关系会产生重复记录，导致金额被重复计算。

SELECT
    c.Name,
    COALESCE(i.Spent, 0) AS Spent,
    COALESCE(p.Paid, 0) AS Paid,
    COALESCE(i.Spent, 0) - COALESCE(p.Paid, 0) AS Total
FROM Clients AS c
LEFT JOIN (
    SELECT
        ClientId,
        SUM(Amount) AS Spent
    FROM Invoices
    GROUP BY ClientId
) AS i
    ON i.ClientId = c.Id
LEFT JOIN (
    SELECT
        ClientId,
        SUM(Paid) AS Paid
    FROM Payments
    GROUP BY ClientId
) AS p
    ON p.ClientId = c.Id;

查询结果如下：

Name   Spent  Paid  Total
John     200   240    -40
Tom      250   110    140
Peter    100     0    100
Bob       20    20      0

为什么要先分别汇总

假设直接连接明细表：

FROM Clients c
JOIN Invoices i ON i.ClientId = c.Id
LEFT JOIN Payments p ON p.ClientId = c.Id

如果同一客户有多张发票和多笔付款，每张发票都会与每笔付款组合。比如某位客户有 2 张发票和 3 笔付款，连接后将产生 6 行。此时再计算 SUM(i.Amount) 和 SUM(p.Paid)，发票金额和付款金额都会被重复累计。

正确的顺序是：

按客户汇总发票，每个客户得到一行。
按客户汇总付款，每个客户得到一行。
根据 ClientId 连接两组汇总结果。
用 Spent - Paid 计算尚欠金额。

`COALESCE()` 的作用

有些客户可能没有付款记录。使用 LEFT JOIN 后，这类客户的 p.Paid 会是 NULL。任何数字与 NULL 运算，结果仍然是 NULL。

COALESCE(p.Paid, 0)

这段表达式会将缺少付款记录的情况按 0 处理。发票金额采用相同的写法后，即使客户没有发票，查询也能返回数值结果。

MySQL 8.0 的 CTE 写法

在支持公用表表达式的 MySQL 版本中，也可以使用下面这种更清晰的写法：

WITH invoice_totals AS (
    SELECT
        ClientId,
        SUM(Amount) AS Spent
    FROM Invoices
    GROUP BY ClientId
),
payment_totals AS (
    SELECT
        ClientId,
        SUM(Paid) AS Paid
    FROM Payments
    GROUP BY ClientId
)
SELECT
    c.Name,
    COALESCE(i.Spent, 0) AS Spent,
    COALESCE(p.Paid, 0) AS Paid,
    COALESCE(i.Spent, 0) - COALESCE(p.Paid, 0) AS Total
FROM Clients AS c
LEFT JOIN invoice_totals AS i
    ON i.ClientId = c.Id
LEFT JOIN payment_totals AS p
    ON p.ClientId = c.Id;

这两种写法的计算逻辑相同。

只返回有发票的客户

如果数据中可能包含没有发票的客户，而查询结果只需要保留有发票的客户，可以将发票汇总部分改为 INNER JOIN：

SELECT
    c.Name,
    i.Spent,
    COALESCE(p.Paid, 0) AS Paid,
    i.Spent - COALESCE(p.Paid, 0) AS Total
FROM Clients AS c
INNER JOIN (
    SELECT
        ClientId,
        SUM(Amount) AS Spent
    FROM Invoices
    GROUP BY ClientId
) AS i
    ON i.ClientId = c.Id
LEFT JOIN (
    SELECT
        ClientId,
        SUM(Paid) AS Paid
    FROM Payments
    GROUP BY ClientId
) AS p
    ON p.ClientId = c.Id;

注意事项

Total 为正数时，表示客户仍有欠款。
Total 为 0 时，表示发票与付款已经结清。
Total 为负数时，表示付款超过发票总额，可视为预付款或账户余额。
财务金额宜使用 DECIMAL 字段，不要使用 FLOAT 或 DOUBLE。
可以为 Invoices.ClientId 和 Payments.ClientId 建立索引，提高分组和连接效率。
这条查询计算的是客户级余额，无法判断某张发票是否已经结清。如果需要逐张发票追踪付款，应增加付款分配明细表，例如 PaymentAllocations(PaymentId, InvoiceId, Amount)。