典型的银行任务

Question

我认为这是银行领域的常见任务。我需要填充'Income''Outcome'。但是每个"结果"值都是这样计算的结果收入=+借方信贷从当前行(每一行)。

我想我应该使用lag()'Income'。但这会在计算中产生周期性。

我希望这能帮到你:

create table account(acc_date date,income int, debit int, credit int, outcome int); insert into account values('2021-01-01', 100,800,500,400), ('2021-02-01', null,900,1500,null), ('2021-03-01', null,1700,2000,null), ('2021-04-01', null,2100,2800,null), ('2021-05-01', null,3500,4000,null); select * from account;

Answer 1

未经测试，但使用sum() over()并与lag() over()合并

with cte as (
Select * 
,OutCome = sum( isnull(Income,0)+Debit-Credit ) over (order by date)
From YourTable
)
Select Date
,Income = coalesce(Income,lag(outcome,1) over (order by date))
,Credit
,Debit
,OutCome
From  cte