-- 範例資料
TxnID | TxnDate | Amount
1 | 2006-11-03 02:33:42 | 10000.00
2 | 2006-11-03 02:34:50 | -500.00
3 | 2006-11-03 02:35:04 | 250.00
4 | 2006-11-03 02:42:19 | -124.25
SELECT t1.TxnID, t1.TxnDate, t1.Amount,
(SELECT SUM(t2.Amount) FROM Transactions t2 WHERE t2.TxnID <= t1.TxnID) AS Balance
FROM Transactions t1
ORDER BY t1.TxnID;
缺點:O(N²) 掃描,大量資料極慢;優點:版本相容性極佳。
SELECT TxnID, TxnDate, Amount,
SUM(Amount) OVER (ORDER BY TxnID ROWS UNBOUNDED PRECEDING) AS Balance
FROM Transactions
ORDER BY TxnID;
ROWS UNBOUNDED PRECEDING:從分區起始到當前行 (含);PARTITION BY AccountID 多帳戶並行計算。TxnID TxnDate Amount Balance
1 2006-11-03 02:33:42.340 10000.00 10000.00
2 2006-11-03 02:34:50.467 -500.00 9500.00
3 2006-11-03 02:35:04.857 250.00 9750.00
4 2006-11-03 02:42:19.763 -124.25 9625.75
同樣模式:SUM(Qty) OVER (PARTITION BY ProductID ORDER BY TxnDate ROWS UNBOUNDED PRECEDING) 即得即時庫存/累計銷售。