SQL 按 account_num 和 item_order 分配 payment

2026-10-24支付阿杰

结论

期望结果与“每个账户只有一笔 cr_amt，并按 item_order 依次分配”的规则不一致。

以账户 12 为例，cr_amt = 1000。第一张 invoice 已经分配了全部 1000，第二张不可能再分配 256。账户 13 的第三张 invoice 金额为 156，此时还剩 200，且该 invoice 不允许部分支付。按照规则，这一行应全额分配 156，而不是 100。

如果业务规则如下：

每个账户的 cr_amt 只计算一次；
按 item_order 顺序处理；
Partial_pmt = 'Y' 时允许部分支付；
Partial_pmt = 'N' 时，余额足够则全额支付，否则跳过；
跳过的 invoice 不消耗余额；

可以用递归 CTE 实现，无需使用游标或过程式 loop。

SQL Server 示例

假设表名为 invoice，且同一账户内的 item_order 唯一：

WITH ordered_invoice AS
(
    SELECT
        account_num,
        item_order,
        inv_amount,
        partial_pmt,
        cr_amt,
        ROW_NUMBER() OVER (
            PARTITION BY account_num
            ORDER BY item_order
        ) AS rn
    FROM invoice
),
payment_distribution AS
(
    -- 每个账户的第一张 invoice
    SELECT
        account_num,
        item_order,
        inv_amount,
        partial_pmt,
        cr_amt,
        rn,
        CAST(
            CASE
                WHEN partial_pmt = 'Y'
                    THEN CASE
                             WHEN cr_amt < inv_amount THEN cr_amt
                             ELSE inv_amount
                         END
                WHEN cr_amt >= inv_amount
                    THEN inv_amount
                ELSE 0
            END
            AS decimal(18, 2)
        ) AS dist_amt,
        CAST(
            cr_amt -
            CASE
                WHEN partial_pmt = 'Y'
                    THEN CASE
                             WHEN cr_amt < inv_amount THEN cr_amt
                             ELSE inv_amount
                         END
                WHEN cr_amt >= inv_amount
                    THEN inv_amount
                ELSE 0
            END
            AS decimal(18, 2)
        ) AS remaining_credit
    FROM ordered_invoice
    WHERE rn = 1

    UNION ALL

    -- 依次处理该账户的后续 invoice
    SELECT
        i.account_num,
        i.item_order,
        i.inv_amount,
        i.partial_pmt,
        i.cr_amt,
        i.rn,
        CAST(x.dist_amt AS decimal(18, 2)) AS dist_amt,
        CAST(
            p.remaining_credit - x.dist_amt
            AS decimal(18, 2)
        ) AS remaining_credit
    FROM payment_distribution AS p
    JOIN ordered_invoice AS i
      ON i.account_num = p.account_num
     AND i.rn = p.rn + 1
    CROSS APPLY
    (
        SELECT
            CASE
                WHEN i.partial_pmt = 'Y'
                    THEN CASE
                             WHEN p.remaining_credit < i.inv_amount
                                 THEN p.remaining_credit
                             ELSE i.inv_amount
                         END
                WHEN p.remaining_credit >= i.inv_amount
                    THEN i.inv_amount
                ELSE 0
            END AS dist_amt
    ) AS x
)
SELECT
    account_num,
    item_order,
    inv_amount,
    partial_pmt,
    dist_amt,
    inv_amount - dist_amt AS invoice_balance,
    remaining_credit
FROM payment_distribution
ORDER BY account_num, item_order
OPTION (MAXRECURSION 0);

按上述规则得到的结果

account_num item_order inv_amount partial_pmt dist_amt invoice_balance remaining_credit
----------- ---------- ---------- ----------- -------- ---------------- ----------------
12          1          1256       Y           1000     256              0
12          2          1134       Y           0        1134             0
13          1          800        Y           800      0                400
13          2          200        N           200      0                200
13          3          156        N           156      0                44

这里有两种余额，需要分别处理：

invoice_balance = inv_amount - dist_amt：当前 invoice 尚未支付的金额。
remaining_credit：该账户仍可分配的金额。

原期望结果中的 Bal_amt 混用了这两种含义，无法通过一套一致的分配规则得出。

关于“大于”还是“大于等于”

通常，“不允许部分支付”是指余额至少等于 invoice 金额时可以支付，因此条件应写成：

remaining_credit >= inv_amount

如果业务规则明确要求余额必须严格大于 invoice 金额才能支付，则把两处 >= 改为 >：

remaining_credit > inv_amount

修改后，余额恰好等于 invoice 金额时，该行也会被跳过。

注意事项

递归处理的顺序必须稳定。如果同一账户可能有相同的 item_order，需要在窗口排序中加入唯一列，例如 invoice_id：

ROW_NUMBER() OVER (
    PARTITION BY account_num
    ORDER BY item_order, invoice_id
)

还要确保同一 account_num 各行的 cr_amt 一致。更合理的数据模型是把账户 credit 存放在账户表中，再与 invoice 表关联，避免重复存储导致不同 invoice 上的 cr_amt 不一致。