SQL Server 查詢對上(或下)一行的計算(例如相加或相減等)

這篇要來記錄一下用 SQL Server 來查詢出
對上(或下)一行的計算(例如相加或相減等)，

例如：
我們現在有一個 Table ，有 id 和 amount 兩個 INT 的欄位，
放了三筆資料，如下所示：

id	amount
3	45
2	25
1	10

我們希望在 id 由大到小排序的情況，得出 amount 一行與上一行的差值，也就是結果應該要像這樣：

increase

id	current amount	previous amount	increase
3	45	25	20
2	25	10	15
1	10	0	10

上面的表列出了某一行的 amount (current amount) 、上一行的 amount (previous amount) 還有
之前的差值 (increase)。

下面直接上 SQL 語法：

-- 先建立測試用 Table
DECLARE @tempTable TABLE (
	id int not null,
	amount int not null,
	PRIMARY KEY (id)
)
-- 塞入測試資料
INSERT INTO @tempTable(id, amount) VALUES(1, 10)
INSERT INTO @tempTable(id, amount) VALUES(2, 25)
INSERT INTO @tempTable(id, amount) VALUES(3, 45)
-- 檢查一下
SELECT *
FROM @tempTable
ORDER BY id DESC;
-- 進行差值計算，
-- 利用 ROW_NUMBER() 來為每行標上序號值 (rowNumber)，
-- 得到的 Table 自己跟自己用 JOIN，JOIN 規則是 Table 的某行 (假設序號是 i) 和上一行 (序號是 i - 1) 做 JOIN
WITH romNumberTable AS (
	SELECT *, ROW_NUMBER() OVER (ORDER BY id DESC) AS rowNumber
	FROM (
		SELECT id, amount
		FROM @tempTable
	) romNumberTable
)
SELECT T1.id, T1.amount AS 'current amount', ISNULL(T2.amount, 0) AS 'previous amount', T1.amount - ISNULL(T2.amount, 0) AS 'increase'
FROM romNumberTable T1 LEFT JOIN romNumberTable T2 ON T1.id = T2.id + 1
ORDER BY id DESC

說明：
我們利用 ROW_NUMBER 將排序好的 Table 標上序號，得到了像這樣的 Table：

id	amount	rowNumber
3	45	1
2	25	2
1	10	3

然後 Table 自己跟自己做 JOIN，JOIN 規則就是 rowNumber 和 rowNumber - 1 做 JOIN，
這樣就可以將 Table 的某行跟上一行 JOIN 再一起了，JOIN 後的結果會像這樣：

id - current	amount - current	rowNumber - current	id - previous	amount - previous	rowNumber - previous
3	45	1	2	25	2
2	25	2	1	10	3
1	10	3

可以注意到最後一行因為沒有上一行所以 JOIN 不到東西，所以我們使用 LEFT JOIN 來保留沒有東西 JOIN 的那行資料。

成功 JOIN 後，就可以自由地做我們想要做的計算了。

參考資料：

1. SQL如何计算当前行减去上一行的值