PAYATHON 2026

SQL Server Stored procedure：用收款抵扣现有欠款

支付小周

结论

不需要使用 CURSOR。可以先用窗口函数计算每笔欠款之前的累计余额，再通过一次集合化 UPDATE，按日期顺序抵扣付款。

如果同一天有多笔欠款，必须使用唯一且稳定的字段（如 BillId）确定先后顺序。仅按 D_Date 排序，抵扣顺序并不确定。

以下实现采用题目中的更新规则：

  • 付款已经覆盖的记录，将 N_Amount 更新为 0；
  • 完全抵扣的记录，将 Amount_Rest 更新为 0；
  • 部分抵扣的记录，在 Amount_Rest 中保留尚欠金额；
  • 付款尚未抵扣到的记录保持不变。

存储过程示例

假设表中至少有以下字段：

  • BillId：唯一主键；
  • D_Date：欠款日期；
  • N_Amount：原题中的 Amount；
  • Amount_Rest：当前剩余欠款。
CREATE OR ALTER PROCEDURE dbo.ApplyPayment
    @PaymentAmount decimal(19, 2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @PaymentAmount IS NULL OR @PaymentAmount <= 0
    BEGIN
        THROW 50001, 'Payment amount must be greater than zero.', 1;
    END;

    BEGIN TRANSACTION;

    ;WITH OrderedBills AS
    (
        SELECT
            b.BillId,
            b.Amount_Rest,
            PreviousBalance =
                COALESCE(
                    SUM(b.Amount_Rest) OVER
                    (
                        ORDER BY b.D_Date, b.BillId
                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                    ),
                    CONVERT(decimal(19, 2), 0)
                )
        FROM dbo.FD_Bills AS b WITH (UPDLOCK, HOLDLOCK)
        WHERE b.Amount_Rest > 0
    )
    UPDATE b
    SET
        b.N_Amount =
            CASE
                WHEN @PaymentAmount > ob.PreviousBalance
                    THEN CONVERT(decimal(19, 2), 0)
                ELSE b.N_Amount
            END,
        b.Amount_Rest =
            CASE
                -- 付款尚未抵扣到当前记录
                WHEN @PaymentAmount <= ob.PreviousBalance
                    THEN b.Amount_Rest

                -- 付款足以完全抵扣当前记录
                WHEN @PaymentAmount >= ob.PreviousBalance + ob.Amount_Rest
                    THEN CONVERT(decimal(19, 2), 0)

                -- 付款只能部分抵扣当前记录
                ELSE ob.PreviousBalance
                     + ob.Amount_Rest
                     - @PaymentAmount
            END
    FROM dbo.FD_Bills AS b
    INNER JOIN OrderedBills AS ob
        ON ob.BillId = b.BillId;

    COMMIT TRANSACTION;
END;
GO

调用方式：

EXEC dbo.ApplyPayment
    @PaymentAmount = 250.00;

假设最早的两笔欠款都是 150.00，抵扣过程如下：

  1. 第一笔抵扣 150.00，还剩 100.00 可用于付款；
  2. 第二笔抵扣 100.00，这笔欠款还剩 50.00；
  3. 付款用完，后面的记录不变。

更新后，前两条记录为：

D_Date     N_Amount  Amount_Rest
---------- --------- -----------
2018-12-05 0.00      0.00
2018-12-05 0.00      50.00

计算原理

PreviousBalance 是当前记录之前所有欠款的累计余额：

SUM(Amount_Rest) OVER
(
    ORDER BY D_Date, BillId
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
)

处理当前记录时：

  • 如果付款金额不大于 PreviousBalance，说明付款已经被前面的记录用完，当前记录不变；
  • 如果付款金额大于等于 PreviousBalance + Amount_Rest，说明当前记录可以全部抵扣；
  • 如果付款金额介于两者之间，当前记录只能部分抵扣。

这段 SQL 会一次处理所有记录，不需要逐行操作游标，也能避免遗漏最后一条记录或因游标读取顺序不明确而出错。

如果 N_Amount 表示原始账单金额

原始账单金额通常需要保留，只更新 Amount_Rest。如果 N_Amount 保存的是账单最初的金额，而非本次处理后的金额，可以删除对 N_Amount 的更新：

UPDATE b
SET b.Amount_Rest =
    CASE
        WHEN @PaymentAmount <= ob.PreviousBalance
            THEN b.Amount_Rest
        WHEN @PaymentAmount >= ob.PreviousBalance + ob.Amount_Rest
            THEN CONVERT(decimal(19, 2), 0)
        ELSE ob.PreviousBalance
             + ob.Amount_Rest
             - @PaymentAmount
    END
FROM dbo.FD_Bills AS b
INNER JOIN OrderedBills AS ob
    ON ob.BillId = b.BillId;

这样处理后，第一条记录的原始金额仍为 150.00，剩余欠款则变为 0.00。从账务审计的角度看，保留原始账单金额通常更合理。

注意事项

  • 不要只用日期排序。同一天有多条记录时，还应加入 BillId、创建时间或业务序号。
  • 金额建议使用 decimal(19, 2)，避免使用近似数值类型。现有的 money 类型可以继续使用，但明确指定精度的 decimal 通常更方便控制计算规则。
  • 事务与 UPDLOCK, HOLDLOCK 可以避免两笔付款同时读取相同余额并重复抵扣。
  • 如果表中包含多个客户或账户，需要增加账户参数，并在查询中加入 WHERE CustomerId = @CustomerId。
  • 如果付款金额大于全部欠款，这个过程会将所有欠款清零，但不会记录多出的金额。需要保留预付款时，应计算剩余付款，并将其写入单独的付款表或账户余额表。
  • 实际账务系统最好同时保存付款明细以及付款与账单的分摊关系，而不只是修改余额。这样才能查清每笔付款抵扣了哪些账单。

备注：内容仅供参考。