MySQL 多对多关系中避免重复聚合 payments 与 invoices

结论

这是典型的多对多金额分配关系。Junction table 不能只保存 payment_id 和 invoice_id，还需要增加 allocated_amount，记录某笔 payment 实际分配给某张 invoice 的金额。

问题不在 GROUP BY，而是当前数据模型没有记录金额如何分配。invoice_total 是整张 invoice 的金额，payment_total 是整笔 payment 的金额。经过多对多连接后，这两个值重复出现很正常。无论使用 DISTINCT、更改分组字段还是嵌套聚合，都无法可靠推算每条关联对应的实际金额。

推荐的数据模型

可以将 junction table 命名为 payment_allocations，让它的业务含义更清楚：

CREATE TABLE payments (
    payment_id BIGINT UNSIGNED PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    paid_at DATETIME NOT NULL
);

CREATE TABLE invoices (
    invoice_id BIGINT UNSIGNED PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    invoice_total DECIMAL(15, 2) NOT NULL,
    issued_at DATETIME NOT NULL
);

CREATE TABLE payment_allocations (
    payment_id BIGINT UNSIGNED NOT NULL,
    invoice_id BIGINT UNSIGNED NOT NULL,
    allocated_amount DECIMAL(15, 2) NOT NULL,

    PRIMARY KEY (payment_id, invoice_id),

    CONSTRAINT fk_allocation_payment
        FOREIGN KEY (payment_id)
        REFERENCES payments(payment_id),

    CONSTRAINT fk_allocation_invoice
        FOREIGN KEY (invoice_id)
        REFERENCES invoices(invoice_id),

    CONSTRAINT chk_allocated_amount_positive
        CHECK (allocated_amount > 0)
);

如果同一笔 payment 可以多次分配到同一张 invoice，例如需要保留分配调整记录，就不应使用复合主键。此时可以增加独立的 allocation_id：

CREATE TABLE payment_allocations (
    allocation_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_id BIGINT UNSIGNED NOT NULL,
    invoice_id BIGINT UNSIGNED NOT NULL,
    allocated_amount DECIMAL(15, 2) NOT NULL,

    FOREIGN KEY (payment_id) REFERENCES payments(payment_id),
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id),

    CHECK (allocated_amount > 0)
);

如果不需要保留分配过程，使用复合主键会更简单。

两种情况如何记录

一笔 $600 的 payment 支付两张 invoices：

INSERT INTO payment_allocations
    (payment_id, invoice_id, allocated_amount)
VALUES
    (101, 201, 250.00),
    (101, 202, 350.00);

一张 $600 的 invoice 由两笔 payments 支付：

INSERT INTO payment_allocations
    (payment_id, invoice_id, allocated_amount)
VALUES
    (102, 203, 250.00),
    (103, 203, 350.00);

这两种情况可以使用同一种模型，无需增加新的业务实体，也无需对少见的 legacy case 做特殊编码。

按 payment 聚合

查看每笔 payment 已分配给 invoices 的金额：

SELECT
    p.payment_id,
    p.amount AS payment_total,
    COALESCE(SUM(pa.allocated_amount), 0) AS allocated_total,
    p.amount - COALESCE(SUM(pa.allocated_amount), 0) AS unallocated_amount
FROM payments AS p
LEFT JOIN payment_allocations AS pa
    ON pa.payment_id = p.payment_id
GROUP BY
    p.payment_id,
    p.amount;

这里应当对 allocated_amount 求和，而不是对 invoices.invoice_total 求和。

LEFT JOIN 会保留尚未分配到任何 invoice 的 payment。unallocated_amount 表示尚未分配的 payment 金额，不一定是 commission。

如果还要显示涉及的 invoices 数量：

SELECT
    p.payment_id,
    p.amount AS payment_total,
    COUNT(DISTINCT pa.invoice_id) AS invoice_count,
    COALESCE(SUM(pa.allocated_amount), 0) AS allocated_total,
    p.amount - COALESCE(SUM(pa.allocated_amount), 0) AS unallocated_amount
FROM payments AS p
LEFT JOIN payment_allocations AS pa
    ON pa.payment_id = p.payment_id
GROUP BY
    p.payment_id,
    p.amount;

按 invoice 聚合

计算每张 invoice 的已支付金额和未支付金额：

SELECT
    i.invoice_id,
    i.invoice_total,
    COALESCE(SUM(pa.allocated_amount), 0) AS paid_total,
    i.invoice_total - COALESCE(SUM(pa.allocated_amount), 0) AS balance_due
FROM invoices AS i
LEFT JOIN payment_allocations AS pa
    ON pa.invoice_id = i.invoice_id
GROUP BY
    i.invoice_id,
    i.invoice_total;

这个查询同时适用于全额支付、部分支付，以及由多笔 payments 支付同一张 invoice 的情况。

查询尚未结清的 invoices：

SELECT
    i.invoice_id,
    i.invoice_total,
    COALESCE(SUM(pa.allocated_amount), 0) AS paid_total,
    i.invoice_total - COALESCE(SUM(pa.allocated_amount), 0) AS balance_due
FROM invoices AS i
LEFT JOIN payment_allocations AS pa
    ON pa.invoice_id = i.invoice_id
GROUP BY
    i.invoice_id,
    i.invoice_total
HAVING balance_due > 0;

查看具体分配明细

如果需要同时显示 payment、invoice 和每次分配的金额，可以直接查询分配表：

SELECT
    pa.payment_id,
    p.amount AS payment_total,
    pa.invoice_id,
    i.invoice_total,
    pa.allocated_amount
FROM payment_allocations AS pa
JOIN payments AS p
    ON p.payment_id = pa.payment_id
JOIN invoices AS i
    ON i.invoice_id = pa.invoice_id
ORDER BY
    pa.payment_id,
    pa.invoice_id;

结果中的 payment_total 和 invoice_total 仍可能重复，但这只是展示上的重复。真正可以聚合的度量是每条分配关系自己的 allocated_amount。

为什么原查询无法修正

原查询将每张 invoice 的完整金额计入了与它关联的每一笔 payment：

SUM(invoice_total)

假设一张 $600 的 invoice 由 $250 和 $350 两笔 payments 支付，连接结果会在两笔 payment 下分别出现一次 $600。数据库只知道这两笔 payment 都与该 invoice 有关，并不知道它们分别承担了 $250 和 $350。

同样，如果按 invoice_id 对完整的 payment 金额求和，那么一笔 payment 支付多张 invoices 时也会发生重复计算。

SUM(DISTINCT invoice_total) 同样解决不了这个问题。例如，两张不同的 invoices 金额恰好都是 $250，DISTINCT 会错误地只计算一次。它去除的是相同数值，而不是重复的业务实体，也无法表达部分分配。

因此，如果没有 allocated_amount 或其他等价信息，就不存在通用、可靠的查询解法。

commission 和 bank fee 应单独定义

下面的计算结果通常不能直接视为 commission：

SUM(invoice_total) - payment_total

Invoice 金额、客户支付金额、银行实际到账金额和手续费是不同的概念，需要分别明确业务定义。

例如，可以在 payments 中记录：

gross_amount       -- 客户支付的原始金额
settled_amount     -- 银行实际结算到账金额
bank_fee           -- 银行手续费

它们之间可能存在以下关系：

gross_amount = settled_amount + bank_fee

这是常见模型，但是否成立取决于支付渠道、退款、货币转换和手续费承担方式，不能将它当作普遍规则。

Invoice 的核销金额通常来自：

SUM(payment_allocations.allocated_amount)

银行手续费通常取自 payment 或 settlement 数据，而不是 invoice 总额与 payment 总额之差。如果业务中的 commission 确实按某种差额计算，也应为它设置明确的名称和计算口径，以免将未分配金额误认为手续费。

数据完整性注意事项

外键和 CHECK 只能保证单条分配记录有效，无法单独约束以下跨行规则：

一笔 payment 的分配合计不能超过 payment.amount
一张 invoice 的分配合计不能超过 invoice.invoice_total
payment 与 invoice 必须属于同一客户

这些规则通常需要在同一个事务中检查和写入：

START TRANSACTION;

SELECT amount
FROM payments
WHERE payment_id = 101
FOR UPDATE;

SELECT invoice_total
FROM invoices
WHERE invoice_id = 201
FOR UPDATE;

/* 检查已有分配合计，然后写入新分配 */
INSERT INTO payment_allocations
    (payment_id, invoice_id, allocated_amount)
VALUES
    (101, 201, 250.00);

COMMIT;

如果只是先查询再插入，却没有锁定相关记录，并发请求可能造成超额分配。具体检查可以在应用服务或存储过程中完成。Trigger 也可以实现部分约束，但跨行汇总、并发控制和错误处理会更难理解，因此不适合作为初学阶段的首选方案。

金额字段应使用 DECIMAL，不要使用 FLOAT 或 DOUBLE。如果系统支持多币种，还要在 payment、invoice 和 allocation 层明确币种及汇率规则，不能直接汇总不同币种的金额。

是否保存 invoice 的 payment_total

通常不建议在 invoices 中维护可由分配记录计算出的 payment_total。同时保存明细和汇总会形成两个事实来源。一旦 trigger、导入脚本或人工修复遗漏更新，数据就会不一致。

应优先通过查询或视图计算汇总值：

CREATE VIEW invoice_payment_summary AS
SELECT
    i.invoice_id,
    i.invoice_total,
    COALESCE(SUM(pa.allocated_amount), 0) AS paid_total,
    i.invoice_total - COALESCE(SUM(pa.allocated_amount), 0) AS balance_due
FROM invoices AS i
LEFT JOIN payment_allocations AS pa
    ON pa.invoice_id = i.invoice_id
GROUP BY
    i.invoice_id,
    i.invoice_total;

只有当数据量和性能测试明确证明实时聚合无法满足要求时，才有必要考虑缓存汇总字段，同时还要设计严格的同步和对账机制。

为 junction table 增加 allocated_amount，是这个场景中简单且信息完整的设计。它既能处理常见的一笔 payment 对应一张 invoice，也能覆盖两个方向的拆分情况。

备注：内容仅供参考。