如何在单个 sql select 语句中添加具有滞后的前一行的值

Question

我有一个包含 A 列和 B 列的 sql 表，第三列 C = A - B。

例如：

Row 1: A = 10, B = 8, C = 2
Row 2: A = 7, B = 8, C = -1
Row 3: A = 0, B = 8, C = -8

我想总结第四列 D 中第三列 C 的值。列 D 应为最后一行的 D 值加上当前行的 C 值。例：

Row 1: A = 10, B = 8, C = 2, D = 2
Row 2: A = 7, B = 8, C = -1, D = 1
Row 3: A = 0, B = 8, C = -8, D = -7

我尝试使用以下 SQL：

SELECT myTable2.*, D = LAG(D) + C OVER (ORDER BY Id)
FROM 
(
    SELECT myTable.*, C = myTable.A - myTable.B
    FROM 
    (
        SELECT Id = 1, A = 10, B = 8
        UNION
        SELECT Id = 2, A = 7, B = 8
        UNION
        SELECT Id = 3, A = 0, B = 8
    ) myTable
) myTable2
ORDER BY myTable2.Id ASC

我可以得到LAG(C)，但不能LAG(D)。任何想法如何实现这一目标？

谢谢。

Answer 1

由于您的数据上没有 id，因此我按 A 降序排序。

SELECT A, B, A-B C, SUM(A-B) OVER (ORDER BY A DESC) D FROM (VALUES (10,8),(7,8),(0,8)) A(A,B)
A           B           C           D
----------- ----------- ----------- -----------
10          8           2           2
7           8           -1          1
0           8           -8          -7
(3 row(s) affected)

Answer 2

你可以试试这个：

SELECT myTable2.*,  D = ISNULL(SUM(C) OVER (ORDER BY Id), 0)
FROM 
(
    SELECT myTable.*, C = myTable.A - myTable.B
    FROM 
    (
        SELECT Id = 1, A = 10, B = 8
        UNION
        SELECT Id = 2, A = 7, B = 8
        UNION
        SELECT Id = 3, A = 0, B = 8
    ) myTable
) myTable2

Answer 3

create table 
#test
(
a int,
b int,
c int
)
insert into #test
select 1,2,3
union all
select 3,4,5
with cte
as
(
select *,0 as 'd', row_number() over (order by a,b,c) as rn
from #test
)
select *,(select sum(c+d) from cte c1 where c1.rn<=c2.rn) 'D'
from cte c2