SQL Server Balance表递归更新问题：无法联动更新修改后后续余额

我有一个SQL Server数据库，其中包含名为Balance的表，表结构及数据如下：

字段说明：

• Id：唯一标识符
• Date：余额日期
• IN：收入金额
• OUT：支出金额
• Balance：前一日余额 + IN - OUT

当修改某一天的IN或OUT值后，该日期及之后所有日期的Balance都需要重新计算以符合规则。比如修改2022-08-04的IN值从20改为100后，2022-08-04及之后的Balance都要联动更新。但当前实现代码只能更新修改当日的余额，无法联动更新后续日期，代码如下：

DECLARE @BalanceDate DATE = '2022-09-04';

DECLARE @BalanceLastDay DECIMAL(19,5) = (SELECT TOP 1 COALESCE(Balance, 0) 
                FROM Balances
                WHERE BalanceDate < @BalanceDate 
                ORDER BY BalanceDate DESC);

WITH Inventory AS 
(
    SELECT Id, BalanceDate, IN, OUT, Balance, 
            LAG(Balance) OVER (ORDER BY BalanceDate) AS BalanceLastDay
    FROM Balances
    WHERE BalanceDate >=  @BalanceDate
),
InventoryUpdated AS
(
    SELECT inv.*, 
    (COALESCE(BalanceLastDay, @BalanceLastDay) + IN - OUT) AS RealBalance
    FROM Inventory inv
)
UPDATE Balances SET Balance = invUpdt.RealBalance
    FROM Balances INNER JOIN InventoryUpdated invUpdt on Balances.Id = invUpdt.Id
    WHERE invUpdt.Balance <> invUpdt.RealBalance;

需要解决如何实现修改数据后递归更新后续所有日期的Balance值。

原代码的问题在于使用LAG(Balance)获取的是原表未更新的旧余额，无法实现后续日期的联动计算。正确做法是用递归CTE从修改日期开始，按日期顺序依次计算每个日期的正确余额，确保后续日期的计算基于前一个日期的更新后余额。

基础版代码（日期连续场景） #

DECLARE @ModifiedDate DATE = '2022-08-04'; -- 替换为实际修改的日期

-- 获取修改日期前一天的最后余额作为初始值
DECLARE @PrevDayBalance DECIMAL(19,5) = (
    SELECT COALESCE(MAX(Balance), 0)
    FROM Balance
    WHERE Date < @ModifiedDate
);

-- 递归CTE计算从修改日期开始的所有正确余额
WITH RecursiveBalance AS (
    -- 锚点成员：计算修改当天的正确余额
    SELECT 
        Id,
        Date,
        IN,
        OUT,
        @PrevDayBalance + IN - OUT AS CorrectBalance
    FROM Balance
    WHERE Date = @ModifiedDate

    UNION ALL

    -- 递归成员：基于前一天的正确余额计算后续日期的余额
    SELECT 
        b.Id,
        b.Date,
        b.IN,
        b.OUT,
        rb.CorrectBalance + b.IN - b.OUT AS CorrectBalance
    FROM Balance b
    INNER JOIN RecursiveBalance rb ON b.Date = DATEADD(DAY, 1, rb.Date)
)

-- 更新原表的Balance字段
UPDATE b
SET b.Balance = rb.CorrectBalance
FROM Balance b
INNER JOIN RecursiveBalance rb ON b.Id = rb.Id;

优化版代码（日期不连续场景） #

如果表中日期存在断档，可先按日期排序生成序号，再进行递归计算：

DECLARE @ModifiedDate DATE = '2022-08-04';

DECLARE @PrevDayBalance DECIMAL(19,5) = (
    SELECT COALESCE(MAX(Balance), 0)
    FROM Balance
    WHERE Date < @ModifiedDate
);

-- 按日期排序生成带序号的数据集
WITH SortedBalances AS (
    SELECT 
        Id,
        Date,
        IN,
        OUT,
        ROW_NUMBER() OVER (ORDER BY Date) AS RowNum
    FROM Balance
    WHERE Date >= @ModifiedDate
),
-- 递归计算正确余额
RecursiveBalance AS (
    SELECT 
        Id,
        Date,
        IN,
        OUT,
        @PrevDayBalance + IN - OUT AS CorrectBalance,
        RowNum
    FROM SortedBalances
    WHERE RowNum = 1

    UNION ALL

    SELECT 
        sb.Id,
        sb.Date,
        sb.IN,
        sb.OUT,
        rb.CorrectBalance + sb.IN - sb.OUT AS CorrectBalance,
        sb.RowNum
    FROM SortedBalances sb
    INNER JOIN RecursiveBalance rb ON sb.RowNum = rb.RowNum + 1
)

UPDATE b
SET b.Balance = rb.CorrectBalance
FROM Balance b
INNER JOIN RecursiveBalance rb ON b.Id = rb.Id;

代码说明 #

1. 初始余额获取：先查询修改日期前一天的最后余额，作为递归计算的起始值。
2. 递归CTE逻辑：
   • 锚点成员计算修改当天的正确余额，基于前一天余额+当日IN-OUT。
   • 递归成员依次关联后续日期，用上一个日期的正确余额计算当前日期的余额，实现全链路联动更新。
3. 更新操作：将递归计算出的正确余额同步回原表。

内容的提问来源于stack exchange，提问作者Henrique Teles Ribeiro