如何根据上一行的值更新值

Question

我有一个如下表(sql server 2019):

Name  Total  Payment   BalanceForward     Date
A     NULL   NULL      0                  NULL
A     20     40        NULL               01-2021
A     100    50        NULL               02-2021
A     50     80        NULL               03-2021
B     NULL   NULL      30                 NULL
B     50     50        NULL               01-2021
B     50     80        NULL               02-2021
B     50     40        NULL               03-2021
C     NULL   NULL      100                NULL
........

我想要的是更新"BalanceForward"列，因为它有Balance Forward of previous row + (current row's Total - current row's Payment)

预期输出:

Name  Total  Payment   BalanceForward   Date
A     NULL   NULL      0                NULL
A     20     40        -20              01-2021
A     100    50        30               02-2021
A     50     80        0                03-2021
B     NULL   NULL      30               NULL
B     50     50        30               01-2021
B     50     80        0                02-2021
B     50     40        10               03-2021
C     NULL   NULL      100              NULL
.......

提供它的最佳方式是什么?如有任何帮助，不胜感激。

Answer 1

看起来您想要实现一个运行总数。您可以通过使用sum over()窗口函数和updatable CTE来实现这一点，如下所示:

with bf as (
select *,
Sum(total-payment) over(partition by name order by date)
+ sum(balanceforward) over(partition by name order by date) newbf
from t
)
update bf set balanceforward=newbf
where balanceforward is null

,DB&lt例子的在小提琴

Answer 2

这是Stu回答的一个更健壮的版本。您可以运行它两次，任意次数都可以得到相同的输出。

with bf as (
select *, sum(total-payment) over(partition by name order by date) + first_value(balanceforward) over(partition by name order by date) newbf
from t
)
update bf set balanceforward=newbf
where date is not null;