Oracle SQL Query：将付款分配到多个 Invoice

结论

可以用 Oracle 分析函数 SUM() OVER() 计算每张 Invoice 的累计金额，再根据区间差值确定当前 Invoice 分到的付款金额。整个过程可以通过一条集合化 SQL 完成，无需编写 PL/SQL 循环。

以下示例基于这些假设：

• Invoice 金额为正数；
• Payment 金额为负数；
• 付款按 ID 从小到大分配；
• 每个用户单独计算和分配；
• 用户名字段为 user_name。如果实际字段名是 "USER"，请相应替换，并注意引号和大小写。

分配逻辑

每张 Invoice 需要用到三个数值：

当前 Invoice 前的累计金额
当前 Invoice 结束时的累计金额
该用户的付款总额

当前 Invoice 的已付金额可按下面的公式计算：

GREATEST(
    LEAST(付款总额, 当前累计 Invoice 金额)
    - 当前 Invoice 之前的累计金额,
    0
)

以示例数据为例：

查询分配结果

WITH payment_totals AS (
    SELECT
        user_name,
        GREATEST(
            -SUM(
                CASE
                    WHEN transaction_type = 'Payment' THEN amount
                    ELSE 0
                END
            ),
            0
        ) AS total_payment
    FROM transactions_register
    GROUP BY user_name
),
invoice_running AS (
    SELECT
        id,
        user_name,
        transaction_type,
        amount,
        SUM(amount) OVER (
            PARTITION BY user_name
            ORDER BY id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_invoice
    FROM transactions_register
    WHERE transaction_type = 'Invoice'
)
SELECT
    i.id,
    i.user_name,
    i.transaction_type,
    i.amount,
    GREATEST(
        LEAST(p.total_payment, i.cumulative_invoice)
            - (i.cumulative_invoice - i.amount),
        0
    ) AS amount_paid
FROM invoice_running i
JOIN payment_totals p
    ON p.user_name = i.user_name
ORDER BY
    i.user_name,
    i.id;

查询结果如下：

ID  USER_NAME  TRANSACTION_TYPE  AMOUNT  AMOUNT_PAID
1   Mr. X      Invoice           1000    1000
2   Mr. X      Invoice           2000    2000
3   Mr. X      Invoice           1000     500

写入目标表

假设目标表已经存在：

CREATE TABLE invoice_payment_alloc (
    id               NUMBER,
    user_name        VARCHAR2(100),
    transaction_type VARCHAR2(20),
    amount           NUMBER,
    amount_paid      NUMBER
);

可以直接使用 INSERT INTO ... WITH ... SELECT 写入数据：

INSERT INTO invoice_payment_alloc (
    id,
    user_name,
    transaction_type,
    amount,
    amount_paid
)
WITH payment_totals AS (
    SELECT
        user_name,
        GREATEST(
            -SUM(
                CASE
                    WHEN transaction_type = 'Payment' THEN amount
                    ELSE 0
                END
            ),
            0
        ) AS total_payment
    FROM transactions_register
    GROUP BY user_name
),
invoice_running AS (
    SELECT
        id,
        user_name,
        transaction_type,
        amount,
        SUM(amount) OVER (
            PARTITION BY user_name
            ORDER BY id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_invoice
    FROM transactions_register
    WHERE transaction_type = 'Invoice'
)
SELECT
    i.id,
    i.user_name,
    i.transaction_type,
    i.amount,
    GREATEST(
        LEAST(p.total_payment, i.cumulative_invoice)
            - (i.cumulative_invoice - i.amount),
        0
    ) AS amount_paid
FROM invoice_running i
JOIN payment_totals p
    ON p.user_name = i.user_name;

如果目标表尚未创建，可以使用 CREATE TABLE AS SELECT，一次完成建表和数据写入：

CREATE TABLE invoice_payment_alloc AS
WITH payment_totals AS (
    SELECT
        user_name,
        GREATEST(
            -SUM(
                CASE
                    WHEN transaction_type = 'Payment' THEN amount
                    ELSE 0
                END
            ),
            0
        ) AS total_payment
    FROM transactions_register
    GROUP BY user_name
),
invoice_running AS (
    SELECT
        id,
        user_name,
        transaction_type,
        amount,
        SUM(amount) OVER (
            PARTITION BY user_name
            ORDER BY id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_invoice
    FROM transactions_register
    WHERE transaction_type = 'Invoice'
)
SELECT
    i.id,
    i.user_name,
    i.transaction_type,
    i.amount,
    GREATEST(
        LEAST(p.total_payment, i.cumulative_invoice)
            - (i.cumulative_invoice - i.amount),
        0
    ) AS amount_paid
FROM invoice_running i
JOIN payment_totals p
    ON p.user_name = i.user_name;

注意事项

分配顺序必须明确。示例按 ID 排序。如果业务要求按 Invoice 日期分配，可以改成：

ORDER BY invoice_date, id

排序字段最好能唯一确定记录顺序。否则，当多张 Invoice 的日期或其他排序值相同时，分配结果可能不稳定。

如果付款总额超过全部 Invoice 的金额，每张 Invoice 最多只会分配到自身金额，多出的付款不会出现在结果中。需要记录未分配余额时，可以另行计算：

GREATEST(
    total_payment - total_invoice_amount,
    0
) AS unapplied_payment

如果 Payment 使用正数保存，付款汇总中的负号需要删除：

SUM(
    CASE
        WHEN transaction_type = 'Payment' THEN amount
        ELSE 0
    END
)

如果批处理会重复执行，直接使用 INSERT 可能产生重复记录。实际应用中可以先按批次清理目标数据，也可以使用 MERGE，根据 Invoice ID 更新已有的分配记录。

备注：内容仅供参考。