sql-server - 根据先前和当前行值计算值

Question

我正在计算帐户数据，其中的值可以通过当前行值和先前行值获得。我的问题是我必须到达两列，其中两列都是相互链接的，两列都是通过当前行值和前一行值获得的。

我的示例数据

create Table Accounts (SNo int,Groups varchar(10),ToCollect int,Collected int)

insert into Accounts values(1,  'A',    4748,   4650)
insert into Accounts values(2,  'A',    4643,   4582)
insert into Accounts values(3,  'A',    4582,   4482)
insert into Accounts values(4,  'A',    4482,   4415)
insert into Accounts values(5,  'A',    4415,   4330)
insert into Accounts values(6,  'A',    4332,   4241)
insert into Accounts values(7,  'A',    4241,   2246)
insert into Accounts values(8,  'A',    4166,   6000)
insert into Accounts values(9,  'A',    4080,   0)

尝试查询:

Select *,case when ToCollect>Collected then Collected Else ToCollect End Adjusted 
    ,case when Collected>ToCollect then Collected-ToCollect Else 0 End Excess into #1
from Accounts

select a.SNo,a.Groups,a.ToCollect,a.Collected
    ,case when a.ToCollect>(a.Collected+b.Excess) then a.Collected+b.Excess Else a.ToCollect End Adjusted 
    ,case when a.Collected+b.Excess>a.ToCollect then a.Collected+b.Adjusted-a.ToCollect Else 0 End Excess
from #1 a left join #1 b on a.SNo=b.SNo-1

实际结果:

SNo Groups  ToCollect   Collected   Adjusted    Excess
1     A       4748        4650         4650       0
2     A       4643        4582         4582       0
3     A       4582        4482         4482       0
4     A       4482        4415         4415       0
5     A       4415        4330         4330       0
6     A       4332        4241         4241       0
7     A       4241        2246         4080       0
8     A       4166        6000         4166      1834
9     A       4080         0           4080        0

预期结果:

 SNo    Groups  ToCollect   Collected   Adjusted    Balance Excess
  1       A       4748        4650        4650        98      0
  2       A       4643        4582        4582        61      0
  3       A       4582        4482        4482        100     0
  4       A       4482        4415        4415        67      0
  5       A       4415        4330        4330        85      0
  6       A       4332        4241        4241        91      0
  7       A       4241        2246        2246        1995    0
  8       A       4166        6000        4166        0       1834
  9       A       4080         0          1834        2246    0

Answer 1

这得到了 OP 想要的结果，但不知道这是否是真的正确答案:

WITH CTE AS(
    SELECT A.SNo,
           A.Groups,
           A.ToCollect,
           A.Collected,
           CASE WHEN A.ToCollect > A.Collected THEN A.Collected ELSE A.ToCollect END AS Adjusted,
           CASE WHEN A.ToCollect > A.Collected THEN A.ToCollect - A.Collected ELSE 0 END AS Balance,
           CASE WHEN A.ToCollect < A.Collected THEN A.Collected - A.ToCollect  ELSE 0 END AS Excess,
           SUM(CASE WHEN A.ToCollect < A.Collected THEN A.Collected - A.ToCollect  ELSE 0 END) OVER (ORDER BY SNo ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS RunningExcess      
    FROM dbo.Accounts A)
SELECT C.SNo,
       C.Groups,
       C.ToCollect,
       C.Collected,
       ISNULL(NULLIF(C.RunningExcess,0),C.Adjusted) AS Adjusted,
       C.Balance - ISNULL(C.RunningExcess,0) AS Balance,
       C.Excess
FROM CTE C;