以下用例使用T-SQL实现。
用以测试的数据表。
CREATE TABLE [dbo].[t_test1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NUM] [int] NOT NULL
) ON [PRIMARY]
准备数据
INSERT INTO t_test1 (NUM) VALUES ( 50 );
INSERT INTO t_test1 (NUM) VALUES ( 126 );
INSERT INTO t_test1 (NUM) VALUES ( 986 );
INSERT INTO t_test1 (NUM) VALUES ( 246 );
INSERT INTO t_test1 (NUM) VALUES ( 5 );
INSERT INTO t_test1 (NUM) VALUES ( 3 );
INSERT INTO t_test1 (NUM) VALUES ( 15 );
INSERT INTO t_test1 (NUM) VALUES ( 66 );
INSERT INTO t_test1 (NUM) VALUES ( 32 );
INSERT INTO t_test1 (NUM) VALUES ( 569 );
INSERT INTO t_test1 (NUM) VALUES ( 23 );
INSERT INTO t_test1 (NUM) VALUES ( 14 );
INSERT INTO t_test1 (NUM) VALUES ( 9 );
INSERT INTO t_test1 (NUM) VALUES ( 35 );
INSERT INTO t_test1 (NUM) VALUES ( 74 );
INSERT INTO t_test1 (NUM) VALUES ( 269 );
INSERT INTO t_test1 (NUM) VALUES ( 90 );
INSERT INTO t_test1 (NUM) VALUES ( 33 );
INSERT INTO t_test1 (NUM) VALUES ( 80 );
INSERT INTO t_test1 (NUM) VALUES ( 91 );
INSERT INTO t_test1 (NUM) VALUES ( 2 );
逐行累加
SELECT ID,
SUM( NUM ) OVER( ORDER BY ID ) as sum_num
FROM t_test1
运行结果
ID sum_num
1 50
2 176
3 1162
4 1408
5 1413
6 1416
7 1431
8 1497
9 1529
10 2098
11 2121
12 2135
13 2144
14 2179
15 2253
16 2522
17 2612
18 2645
19 2725
20 2816
21 2818
逐行相加
SELECT ID, NUM,
NUM + coalesce(lag(num) over ( order by ID ),0) as result
FROM t_test1 as a
运行结果
ID NUM result
1 50 50
2 126 176
3 986 1112
4 246 1232
5 5 251
6 3 8
7 15 18
8 66 81
9 32 98
10 569 601
11 23 592
12 14 37
13 9 23
14 35 44
15 74 109
16 269 343
17 90 359
18 33 123
19 80 113
20 91 171
21 2 93
逐行相减
SELECT ID, NUM,
NUM - coalesce(lag(num) over ( order by ID ),0) as result
FROM t_test1
运行结果
ID NUM result
1 50 50
2 126 76
3 986 860
4 246 -740
5 5 -241
6 3 -2
7 15 12
8 66 51
9 32 -34
10 569 537
11 23 -546
12 14 -9
13 9 -5
14 35 26
15 74 39
16 269 195
17 90 -179
18 33 -57
19 80 47
20 91 11
21 2 -89
是为之记。
Alva Chien
2020.06.12