如何仅使用 SQL 计算每张 invoice 的 payment_date

2026-10-24支付阿杰

结论

如果业务规则是“invoice 对应同一 order_id 下，开票日期之后的第一笔 payment”，可以使用相关子查询，为每张 invoice 找出符合条件的最早付款日期。payment 行不需要执行该查询，因此返回 NULL；没有后续 payment 的 invoice 也会返回 NULL。

SELECT
    t.id,
    t.order_id,
    t.trans_type,
    t.trans_date,
    t.trans_amount,
    CASE
        WHEN t.trans_type = 'invoice' THEN (
            SELECT MIN(p.trans_date)
            FROM `transaction` AS p
            WHERE p.order_id = t.order_id
              AND p.trans_type = 'payment'
              AND p.trans_date >= t.trans_date
        )
        ELSE NULL
    END AS payment_date
FROM `transaction` AS t
ORDER BY t.id;

查询逻辑

查询会逐行执行以下判断：

仅当 trans_type = 'invoice' 时查找付款记录。
payment 与当前 invoice 的 order_id 必须相同。
p.trans_date >= t.trans_date 用来排除开票日期之前的 payment。
MIN(p.trans_date) 返回开票后最早的付款日期。
找不到符合条件的 payment 时，MIN() 返回 NULL。
对于 payment 行，CASE 直接返回 NULL。

示例数据中，order_id=1 的三张 invoice 之后，最早的 payment 都发生在 2017-10-12，所以这三行的 payment_date 相同。order_id=3 没有 payment，因此 invoice 7 的结果为空。

使用 `LEFT JOIN` 的写法

同样的效果也可以通过自连接和聚合实现：

SELECT
    t.id,
    t.order_id,
    t.trans_type,
    t.trans_date,
    t.trans_amount,
    CASE
        WHEN t.trans_type = 'invoice' THEN MIN(p.trans_date)
        ELSE NULL
    END AS payment_date
FROM `transaction` AS t
LEFT JOIN `transaction` AS p
    ON p.order_id = t.order_id
   AND p.trans_type = 'payment'
   AND p.trans_date >= t.trans_date
GROUP BY
    t.id,
    t.order_id,
    t.trans_type,
    t.trans_date,
    t.trans_amount
ORDER BY t.id;

这里需要使用 LEFT JOIN。如果改用 INNER JOIN，尚未付款的 invoice 和原始 payment 记录可能会从查询结果中消失。

注意事项

表名可能是保留字

transaction 在部分数据库中是关键字或具有特殊含义的名称。MySQL 可以写成：

`transaction`

SQL Server 通常使用：

[transaction]

PostgreSQL 等数据库可以使用：

"transaction"

更稳妥的做法是把表重命名为 transactions、order_transactions 等不易产生冲突的名称。

日期应使用日期类型

trans_date 最好定义为 DATE，不要只用字符串存储。虽然 yyyy-mm-dd 格式的字符串通常可以按字典顺序比较，但日期类型能够提供更可靠的校验、比较和索引支持。

建议建立索引

数据量较大时，可以针对查询条件建立组合索引：

CREATE INDEX idx_transaction_order_type_date
ON `transaction` (order_id, trans_type, trans_date);

该方案依赖明确的业务前提

上述查询把“invoice 之后的第一笔 payment”作为该 invoice 的付款日期。这符合示例中的规则，但查询并未根据金额进行核销。

如果存在部分付款、超额付款、一笔付款只覆盖部分 invoice，或者需要按照 FIFO 将付款金额依次分配给 invoice，仅凭 order_id 和日期就无法准确判断每张 invoice 的结清时间。此时需要先明确核销规则，最好增加独立的付款分配表，例如：

payment_allocation
------------------
payment_id
invoice_id
allocated_amount

只有建立 invoice 与 payment 之间的明确对应关系，才能可靠地计算每张 invoice 的实际结清日期。