sql2000数据库中如何实现同一列的数据相减（即下一行数据减去上一行数据）？

Question

如题所述

Answer 1

表结构？ 测试数据？

按哪一列排序？

测试数据.
CREATE TABLE temp (
year INT,
salary INT
);

INSERT INTO temp VALUES(2000, 1000);
INSERT INTO temp VALUES(2001, 2000);
INSERT INTO temp VALUES(2002, 3000);
INSERT INTO temp VALUES(2003, 4000);

预期要求结果：
year salary
2000 1000
2001 1000
2002 1000
2003 10000

SELECT
year,
salary - ISNULL((SELECT TOP 1 salary FROM temp t2 WHERE t2.year < temp.year ORDER BY year DESC), 0) AS salary
FROM
temp;
go

year salary
----------- -----------
2000 1000
2001 1000
2002 1000
2003 1000

(4 行受影响)