SQL Server 2000 按账龄顺序分配客户付款

2026-10-24支付小周

结论

在 SQL Server 2000 中，无需为每位客户逐张循环处理发票。先算出每张发票之前的累计欠款，再根据客户的付款总额计算新余额，最后统一更新即可。

这种做法适用于约 10,000 位客户、每位客户约 20 张发票的情况。表中需要有唯一的发票标识，这样才能在 daysOpen 相同时确定固定的处理顺序。

示例中的付款金额和结果并不一致：

付款为 $500.00 时，结果是 0、4903.33、3457.69。
付款为 $550.00 时，第二张发票的余额应为 4853.33。

以下代码按期望结果处理，将付款额设为 $500.00。

补充唯一发票标识

当前的 id 应该是客户编号，因为同一客户会对应多行记录，不能用它唯一标识发票。可以增加一个代理主键：

ALTER TABLE dbo.openitems
ADD openitem_id int IDENTITY(1,1) NOT NULL
GO

ALTER TABLE dbo.openitems
ADD CONSTRAINT PK_openitems
PRIMARY KEY (openitem_id)
GO

发票按以下顺序处理：

daysOpen 越大，发票越旧，越先处理；
daysOpen 相同时，openitem_id 较小的发票先处理。

如果业务中已经有唯一的发票编号，直接使用该编号即可，无需增加 IDENTITY。

修正示例数据

原始 INSERT 只提供了 5 个值，而表中有 6 列。即使 balance 允许为 NULL，省略列时也要明确写出列名。

INSERT INTO dbo.openitems
    (id, type, date, amount, daysOpen, balance)
VALUES
    ('A12399', 'INV', '2008-12-05', 491.96, 123, 491.96)

INSERT INTO dbo.openitems
    (id, type, date, amount, daysOpen, balance)
VALUES
    ('A12399', 'INV', '2008-12-12', 4911.37, 116, 4911.37)

INSERT INTO dbo.openitems
    (id, type, date, amount, daysOpen, balance)
VALUES
    ('A12399', 'INV', '2008-12-05', 3457.69, 109, 3457.69)

假设每位客户可分配的付款已经汇总到以下表中：

CREATE TABLE dbo.customer_payments
(
    customer_id   varchar(8) NOT NULL,
    payment_amount decimal(12, 2) NOT NULL
)

插入示例付款：

INSERT INTO dbo.customer_payments
    (customer_id, payment_amount)
VALUES
    ('A12399', 500.00)

批量计算并更新余额

SQL Server 2000 不支持 SUM() OVER() 这类窗口函数，可以改用相关子查询，计算当前发票之前的累计金额。

先将计算结果写入临时表，以免更新时读到已经修改过的余额：

BEGIN TRANSACTION

SELECT
    oi.openitem_id,
    oi.amount,
    p.payment_amount,
    prior_amount =
        ISNULL
        (
            (
                SELECT SUM(older.amount)
                FROM dbo.openitems AS older
                WHERE older.id = oi.id
                  AND older.type = 'INV'
                  AND
                  (
                      older.daysOpen > oi.daysOpen
                      OR
                      (
                          older.daysOpen = oi.daysOpen
                          AND older.openitem_id < oi.openitem_id
                      )
                  )
            ),
            0
        )
INTO #allocation
FROM dbo.openitems AS oi
INNER JOIN dbo.customer_payments AS p
    ON p.customer_id = oi.id
WHERE oi.type = 'INV'

CREATE UNIQUE CLUSTERED INDEX IX_allocation
    ON #allocation(openitem_id)

UPDATE oi
SET balance =
    CASE
        -- 付款在处理当前发票之前就已经用完
        WHEN a.payment_amount <= a.prior_amount
            THEN a.amount

        -- 付款足以结清当前发票
        WHEN a.payment_amount >= a.prior_amount + a.amount
            THEN 0.00

        -- 付款只能结清当前发票的一部分
        ELSE a.prior_amount + a.amount - a.payment_amount
    END
FROM dbo.openitems AS oi
INNER JOIN #allocation AS a
    ON a.openitem_id = oi.openitem_id

IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION
    RAISERROR('更新发票余额失败。', 16, 1)
    RETURN
END

COMMIT TRANSACTION

可以用以下查询检查结果：

SELECT
    id,
    amount,
    daysOpen,
    balance
FROM dbo.openitems
WHERE id = 'A12399'
ORDER BY daysOpen DESC, openitem_id

付款为 $500.00 时，余额如下：

0.00
4903.33
3457.69

具体计算过程：

第一张：491.96 - 500.00 = 0.00
剩余付款：500.00 - 491.96 = 8.04

第二张：4911.37 - 8.04 = 4903.33
第三张未分配付款，保持 3457.69

如果实际付款为 $550.00：

剩余付款：550.00 - 491.96 = 58.04
第二张余额：4911.37 - 58.04 = 4853.33

多笔付款需要先按客户汇总

如果付款表中同一客户可能有多条记录，不能直接连接该表，否则同一张发票可能被重复更新。应先按客户汇总付款：

SELECT
    customer_id,
    payment_amount = SUM(payment_amount)
INTO #payments
FROM dbo.customer_payments
GROUP BY customer_id

再将主查询中的：

INNER JOIN dbo.customer_payments AS p

替换为：

INNER JOIN #payments AS p

使用现有余额还是原始金额

上述代码以 amount 作为每张发票的待分配金额，符合问题中的计算方式，适合首次生成 balance。

如果 balance 保存的是当前未结余额，而且同一过程可能执行多次，则应根据更新前的 balance 分配付款，不能每次都重新使用 amount。此时，将计算临时表中的 oi.amount 和 older.amount 分别替换为：

ISNULL(oi.balance, oi.amount)

以及：

SUM(ISNULL(older.balance, older.amount))

还应先把原始余额保存到临时表，再执行更新，避免更新期间计算依据发生变化。

索引与并发注意事项

可以建立一个与客户和账龄顺序相匹配的索引：

CREATE INDEX IX_openitems_customer_age
ON dbo.openitems(id, type, daysOpen, openitem_id)

还要注意以下几点：

daysOpen 相同的发票必须有第二个排序字段，否则分配结果无法确定。
付款金额为 0 或负数时，通常不应进入分配流程。
如果付款超过该客户的发票总额，所有余额都会变为 0.00。剩余款项应另行记录为预收款或未分配款。
计算和更新应放在同一个事务中，防止其他会话同时修改发票或付款。
如果两个任务可能同时处理同一客户，还应使用业务状态、锁或处理批次，避免重复分配。
daysOpen 属于可计算数据。长期来看，按发票日期排序通常更稳妥；如果继续使用 daysOpen，需要保证该列得到正确维护。