SQL Server 計算累計餘額：銀行帳戶交易範例

情境

銀行帳戶交易表記錄存款（正數）與提款（負數），需查詢每筆交易後的累計餘額。

建表腳本

CREATE TABLE dbo.BankAccount (
    TransactionID     INT IDENTITY(1,1) PRIMARY KEY,
    TransactionDate   DATETIME NOT NULL DEFAULT GETDATE(),
    Amount            MONEY NOT NULL DEFAULT 0,
    TransactionType   CHAR(1) NOT NULL,  -- 'D' 存款, 'W' 提款
    AccountNumber     VARCHAR(50) NULL
);

範例資料

INSERT INTO dbo.BankAccount (TransactionDate, Amount, TransactionType) VALUES
('2006-11-03 02:33:42',  10000.00, 'D'),
('2006-11-03 02:34:50',  -500.00,  'W'),
('2006-11-03 02:35:04',   250.00,  'D'),
('2006-11-03 02:42:19',  -124.25, 'W');

方法一：相關子查詢（所有版本通用）

SELECT 
    ba.TransactionID,
    ba.TransactionDate,
    ba.Amount,
    (SELECT SUM(Amount) FROM dbo.BankAccount WHERE TransactionID <= ba.TransactionID) AS RunningBalance
FROM dbo.BankAccount ba
ORDER BY ba.TransactionID;

方法二：視窗函數 SUM() OVER（SQL Server 2012+ 推薦）

SELECT 
    TransactionID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER (ORDER BY TransactionID ROWS UNBOUNDED PRECEDING) AS RunningBalance
FROM dbo.BankAccount
ORDER BY TransactionID;

• ROWS UNBOUNDED PRECEDING 明確指定框架為「從第一列到目前列」，效能優於預設 RANGE。
• 可加入 PARTITION BY AccountNumber 支援多帳戶並行計算。

方法三：自我聯結（舊版替代方案）

SELECT 
    a.TransactionID,
    a.TransactionDate,
    a.Amount,
    SUM(b.Amount) AS RunningBalance
FROM dbo.BankAccount a
JOIN dbo.BankAccount b ON b.TransactionID <= a.TransactionID
GROUP BY a.TransactionID, a.TransactionDate, a.Amount
ORDER BY a.TransactionID;

效能較差（O(N²)），僅供教學參考。

結果對照

進階：依日期分組的每日結餘

SELECT 
    CAST(TransactionDate AS DATE) AS TxnDate,
    SUM(Amount) AS DailyNet,
    SUM(SUM(Amount)) OVER (ORDER BY CAST(TransactionDate AS DATE) ROWS UNBOUNDED PRECEDING) AS ClosingBalance
FROM dbo.BankAccount
GROUP BY CAST(TransactionDate AS DATE)
ORDER BY TxnDate;

總結

SQL Server 2012+ 請直接使用 SUM() OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)，語法簡潔、效能最佳、支援分區。舊版本可用相關子查詢或臨時表迴圈實現。