SQL Server 2014 中批量更新多个 running totals

2026-10-24支付老李

结论

不需要使用 loop 或 cursor。单条 UPDATE 对每个目标行只修改一次，但可以先算出该行受到的累计撤销金额，再一次性减去合计值。

核心公式如下：

新的 running total
= 原 running total
- SUM(所有排序日期不晚于当前行日期的待取消交易金额)

三个日期维度分别计算各自的累计撤销金额。先将待取消交易物化到临时表，并排除已经取消的行，就能保证操作幂等，重复执行时不会再次扣减。

准备待取消的数据

SQL Server 2014 没有 STRING_SPLIT，因此存储过程参数更适合使用 table-valued parameter，不建议传入 CSV 字符串。

CREATE TYPE dbo.TransactionIdList AS TABLE
(
    Id INT NOT NULL PRIMARY KEY
);
GO

在存储过程中，将确实需要取消的交易复制到临时表：

CREATE TABLE #Cancel
(
    Id              INT      NOT NULL PRIMARY KEY,
    EventDate       DATETIME NOT NULL,
    TransactionDate DATETIME NULL,
    PaymentDate     DATETIME NULL,
    Amount          BIGINT   NOT NULL
);

INSERT #Cancel
(
    Id,
    EventDate,
    TransactionDate,
    PaymentDate,
    Amount
)
SELECT
    t.Id,
    t.EventDate,
    t.TransactionDate,
    t.PaymentDate,
    CONVERT(BIGINT, COALESCE(t.Amount, 0))
FROM dbo.MyTransaction AS t
JOIN @Ids AS i
    ON i.Id = t.Id
WHERE t.IsCancelled = 0;

这里只复制 IsCancelled = 0 的行。否则同一批 ID 再次提交时，running total 会被重复扣减。

合计使用 BIGINT，可以避免多个 INT 金额在 SUM 中相加时溢出。正式系统中的 running total 列通常也应使用 BIGINT 或合适的 DECIMAL。

单条 set-wise UPDATE

下面的语句会为每个目标行计算三个累计撤销金额，并且只更新该目标行一次：

UPDATE t
SET
    t.RunningTotalByEventDate =
        t.RunningTotalByEventDate - d.EventDelta,

    t.RunningTotalByTransactionDate =
        t.RunningTotalByTransactionDate - d.TransactionDelta,

    t.RunningTotalByPaymentDate =
        t.RunningTotalByPaymentDate - d.PaymentDelta,

    t.IsCancelled =
        CASE
            WHEN own.Id IS NOT NULL THEN CONVERT(BIT, 1)
            ELSE t.IsCancelled
        END
FROM dbo.MyTransaction AS t
LEFT JOIN #Cancel AS own
    ON own.Id = t.Id
CROSS APPLY
(
    SELECT
        EventDelta =
            COALESCE(SUM(
                CASE
                    WHEN c.EventDate <= t.EventDate
                    THEN c.Amount
                    ELSE CONVERT(BIGINT, 0)
                END
            ), 0),

        TransactionDelta =
            COALESCE(SUM(
                CASE
                    WHEN c.TransactionDate IS NOT NULL
                     AND t.TransactionDate IS NOT NULL
                     AND c.TransactionDate <= t.TransactionDate
                    THEN c.Amount
                    ELSE CONVERT(BIGINT, 0)
                END
            ), 0),

        PaymentDelta =
            COALESCE(SUM(
                CASE
                    WHEN c.PaymentDate IS NOT NULL
                     AND t.PaymentDate IS NOT NULL
                     AND c.PaymentDate <= t.PaymentDate
                    THEN c.Amount
                    ELSE CONVERT(BIGINT, 0)
                END
            ), 0)
    FROM #Cancel AS c
) AS d
WHERE own.Id IS NOT NULL
   OR d.EventDelta <> 0
   OR d.TransactionDelta <> 0
   OR d.PaymentDelta <> 0;

假设要取消两笔金额分别为 30 和 50 的交易，排在它们之后的某个目标行仍然只更新一次，但它的 EventDelta、TransactionDelta 或 PaymentDelta 可以是 80。目标行只更新一次，不代表调整值只能来自一条源记录，它完全可以是多条记录的合计。

为什么使用 `<=`

上面的代码假设 running total 包含当前行：

RunningTotal(current)
= SUM(排序位置不晚于 current 的有效交易)

取消一笔交易时，需要从该交易自身及其后续行中扣除相应金额，因此条件是：

c.EventDate <= t.EventDate

如果表中保存的是不包含当前行的余额，则应改用严格小于：

c.EventDate < t.EventDate

原来的插入逻辑使用 > 更新后续行，是因为新插入行的 running total 通常会在插入时单独初始化。批量取消的情况不同。如果 running total 包含当前行，除了后续行，还要调整被取消行自身。

更适合大表的三次 UPDATE

上面的单条语句比较直观，但它要同时处理三个不同的排序维度。表很大时，拆成三条 set-wise UPDATE 往往更容易利用各自的日期索引，也能避免包含多个日期条件的宽范围扫描。

按 EventDate 更新的写法如下：

UPDATE t
SET t.RunningTotalByEventDate =
    t.RunningTotalByEventDate - d.Delta
FROM dbo.MyTransaction AS t
CROSS APPLY
(
    SELECT Delta = SUM(c.Amount)
    FROM #Cancel AS c
    WHERE c.EventDate <= t.EventDate
) AS d
WHERE d.Delta IS NOT NULL;

另外两个日期可以采用相同的模式：

UPDATE t
SET t.RunningTotalByTransactionDate =
    t.RunningTotalByTransactionDate - d.Delta
FROM dbo.MyTransaction AS t
CROSS APPLY
(
    SELECT Delta = SUM(c.Amount)
    FROM #Cancel AS c
    WHERE c.TransactionDate IS NOT NULL
      AND c.TransactionDate <= t.TransactionDate
) AS d
WHERE d.Delta IS NOT NULL;

UPDATE t
SET t.RunningTotalByPaymentDate =
    t.RunningTotalByPaymentDate - d.Delta
FROM dbo.MyTransaction AS t
CROSS APPLY
(
    SELECT Delta = SUM(c.Amount)
    FROM #Cancel AS c
    WHERE c.PaymentDate IS NOT NULL
      AND c.PaymentDate <= t.PaymentDate
) AS d
WHERE d.Delta IS NOT NULL;

最后设置取消标志：

UPDATE t
SET t.IsCancelled = 1
FROM dbo.MyTransaction AS t
JOIN #Cancel AS c
    ON c.Id = t.Id;

这四条语句仍然属于 set-wise 操作，不是逐行循环。单条组合更新和三条按索引更新哪一种更快，需要结合实际数据分布与执行计划判断，不能只看语句数量。

完整事务边界

running total 的调整和 IsCancelled 的修改必须放在同一个事务中：

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    -- 填充 #Cancel
    -- 更新三个 running totals
    -- 设置 IsCancelled

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

插入、取消和其他会改变 running total 的存储过程还要采用一致的并发控制策略。否则，两个会话可能同时读取旧状态并计算调整值，最终造成余额不一致。

具体可以根据业务吞吐量选择适当的锁提示、SERIALIZABLE 隔离，或者使用 sp_getapplock，将同一账户的余额维护操作串行化。如果只有取消语句使用事务，而插入逻辑不遵守相同规则，并发问题仍然无法彻底解决。

日期重复时必须使用确定性排序

如果数据中可能出现相同日期，只按日期计算 running total 就无法确定唯一顺序。这时应明确使用复合排序键，例如：

ORDER BY EventDate, Id

对应的比较条件也要改为：

c.EventDate < t.EventDate
OR (c.EventDate = t.EventDate AND c.Id <= t.Id)

TransactionDate 和 PaymentDate 也要采用相同处理。索引应按同样的复合顺序建立，例如：

CREATE INDEX IX_MyTransaction_EventDate_Id
ON dbo.MyTransaction(EventDate, Id);

设计上的注意事项

持久化 running total 会带来明显的写放大。交易发生得越早，插入、修改或取消时需要更新的后续行就越多。改用 set-wise SQL 可以消除逐行循环，但无法减少这些实际写入。

如果此类操作很频繁，可以考虑：

保留不可变的交易流水，用冲正记录表示取消；
按账户、月份或其他周期保存余额检查点；
查询时从最近的检查点开始计算，不必每次都从最早记录算起；
分别为三个日期维度维护汇总或检查点结构。

如果继续使用当前表结构，直接替代 cursor 的 set-wise 解法就是：先物化待取消行，再为每个目标行聚合这些取消记录对它造成的累计影响。