求助：使用T-SQL依据前一行计算值更新数据表列

给定如下临时表及初始数据：

declare @MyTable TABLE(ClientId int, RecNo int, PaidAmt decimal(12, 2), PaidBefore decimal(12,2),
    ReimbToDate decimal(12,2), CanReimb decimal(12,2), BalBeforeReimb decimal(12, 2), 
    ReimbAmt decimal(12, 2), BalAfterReimb decimal(12, 2) )

insert into @MyTable (ClientId, RecNo, PaidAmt, PaidBefore, ReimbToDate, CanReimb, BalBeforeReimb ) select 1, 1, 100, 0, 0, 0 ,250
insert into @MyTable (ClientId, RecNo, PaidAmt, PaidBefore, ReimbToDate) select 1, 2, 400, 0, 0
insert into @MyTable (ClientId, RecNo, PaidAmt, PaidBefore, ReimbToDate) select 1, 3,-200 ,0 ,0

insert into @MyTable (ClientId ,RecNo ,PaidAmt ,PaidBefore ,ReimbToDate ,CanReimb,BalBeforeReimb) select 2 ,1 ,900 ,0 ,0 ,0 ,3000
insert into @MyTable (ClientId ,RecNo ,PaidAmt ,PaidBefore ,ReimbToDate) select 2 ,2 ,300 ,0 ,0
insert into @MyTable (ClientId ,RecNo ,PaidAmt ,PaidBefore ,ReimbToDate) select 2 ,3 ,1600 ,0 ,0
insert into @MyTable (ClientId ,RecNo ,PaidAmt ,PaidBefore ,ReimbToDate) select 2 ,4 ,-900 ,900 ,900
insert into @MyTable (ClientId ,RecNo ,PaidAmt ,PaidBefore ,ReimbToDate) select 2 ,5 ,-300 ,300 ,300
insert into @MyTable (ClientId ,RecNo,PaidAmt,PaidBefore,ReimbToDate) select 2,6,750,0,0
insert into @MyTable (ClientId ,RecNo,PaidAmt,PaidBefore,ReimbToDate) select 2,7,625,0,0
insert into @MyTable (ClientId ,RecNo,PaidAmt,PaidBefore,ReimbToDate) select 2,8,125,625,625

需要按照以下规则计算剩余列的值：

• CanReimb = PaidAmt + PaidBefore - ReimbToDate
• 当RecNo≠1时，BalBeforeReimb为同一ClientId的上一行BalAfterReimb的值
• ReimbAmt取CanReimb与BalBeforeReimb的最小值
• BalAfterReimb = BalBeforeReimb - ReimbAmt

期望输出结果如下：

解决方案 #

由于计算依赖于同一客户的上一行结果，使用递归CTE实现逐行计算：

;WITH RecursiveCalculation AS (
    -- 锚点成员：处理每个ClientId的第一行，计算初始值
    SELECT 
        ClientId,
        RecNo,
        PaidAmt,
        PaidBefore,
        ReimbToDate,
        CAST(PaidAmt + PaidBefore - ReimbToDate AS DECIMAL(12,2)) AS CanReimb,
        BalBeforeReimb,
        CAST(IIF(PaidAmt + PaidBefore - ReimbToDate < BalBeforeReimb, PaidAmt + PaidBefore - ReimbToDate, BalBeforeReimb) AS DECIMAL(12,2)) AS ReimbAmt,
        CAST(BalBeforeReimb - IIF(PaidAmt + PaidBefore - ReimbToDate < BalBeforeReimb, PaidAmt + PaidBefore - ReimbToDate, BalBeforeReimb) AS DECIMAL(12,2)) AS BalAfterReimb
    FROM @MyTable
    WHERE RecNo = 1

    UNION ALL

    -- 递归成员：处理后续行，引用上一行的BalAfterReimb作为当前行的BalBeforeReimb
    SELECT 
        mt.ClientId,
        mt.RecNo,
        mt.PaidAmt,
        mt.PaidBefore,
        mt.ReimbToDate,
        CAST(mt.PaidAmt + mt.PaidBefore - mt.ReimbToDate AS DECIMAL(12,2)) AS CanReimb,
        rc.BalAfterReimb AS BalBeforeReimb,
        CAST(IIF(mt.PaidAmt + mt.PaidBefore - mt.ReimbToDate < rc.BalAfterReimb, mt.PaidAmt + mt.PaidBefore - mt.ReimbToDate, rc.BalAfterReimb) AS DECIMAL(12,2)) AS ReimbAmt,
        CAST(rc.BalAfterReimb - IIF(mt.PaidAmt + mt.PaidBefore - mt.ReimbToDate < rc.BalAfterReimb, mt.PaidAmt + mt.PaidBefore - mt.ReimbToDate, rc.BalAfterReimb) AS DECIMAL(12,2)) AS BalAfterReimb
    FROM @MyTable mt
    INNER JOIN RecursiveCalculation rc 
        ON mt.ClientId = rc.ClientId 
        AND mt.RecNo = rc.RecNo + 1
)
-- 输出最终结果，按客户和记录号排序
SELECT * FROM RecursiveCalculation ORDER BY ClientId, RecNo;

说明 #

1. 锚点成员：先处理每个客户的第一行数据，基于初始的BalBeforeReimb计算其余列的值。
2. 递归成员：针对每个客户的后续行，通过关联上一行的结果，将上一行的BalAfterReimb作为当前行的BalBeforeReimb，再依次计算其他列。
3. 最终通过ORDER BY保证结果按客户和记录号的顺序输出，符合业务逻辑。

内容的提问来源于stack exchange，提问作者mak101