SQL Server 累計值查詢實戰：銀行帳戶餘額計算與跑總技巧

核心場景：交易流水的即時餘額計算

銀行帳戶、庫存管理、資金流向分析等場景，都需要根據時間序列計算「截至當前行的累計值」。典型做法是在同一欄位以正負數區分收入/支出，透過跑總 獲取每筆交易後的即時餘額。

範例資料結構

CREATE TABLE [dbo].[BankAccount] (
    [TransactionID] [int] IDENTITY(1,1) NOT NULL,
    [TransactionDateTime] [datetime] NOT NULL CONSTRAINT [DF_BankAccount_TransactionDateTime] DEFAULT (GETDATE()),
    [Amount] [money] NOT NULL CONSTRAINT [DF_BankAccount_Amount] DEFAULT (0),
    [TransactionType] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, -- 'C'存入 / 'D'提領
    [AccountNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [PK_BankAccount] PRIMARY KEY CLUSTERED ([TransactionID] ASC)
) ON [PRIMARY]

測試資料

核心查詢：相關子查詢法

SELECT 
    D0.TransactionID,
    D0.TransactionDateTime,
    D0.Amount,
    (SELECT SUM(D1.Amount) 
     FROM dbo.BankAccount AS D1 
     WHERE D1.TransactionDateTime <= D0.TransactionDateTime) AS Balance
FROM dbo.BankAccount AS D0
ORDER BY D0.TransactionDateTime;

執行結果

進階應用與優化方向

多帳戶支援

新增 AccountNumber 欄位，並在子查詢加入分區條件：

SELECT 
    D0.AccountNumber,
    D0.TransactionID,
    D0.TransactionDateTime,
    D0.Amount,
    (SELECT SUM(D1.Amount) 
     FROM dbo.BankAccount AS D1 
     WHERE D1.AccountNumber = D0.AccountNumber
       AND D1.TransactionDateTime <= D0.TransactionDateTime) AS Balance
FROM dbo.BankAccount AS D0
WHERE D0.AccountNumber = 'A123456'
ORDER BY D0.TransactionDateTime;

庫存扣減場景（反向跑總）

初始庫存 1000，依序扣減出貨量：

SELECT 
    ItemID,
    OutQty,
    1000 - (SELECT SUM(D1.OutQty) 
            FROM InventoryLog AS D1 
            WHERE D1.LogDate <= D0.LogDate) AS CurrentStock
FROM InventoryLog AS D0;

此模式的兩大優勢

• 零儲存冗餘：餘額即時計算，無需維護觸發器或排程更新，資料一致性有保障。配合帳號索引，查詢速度極快。
• 完整稽核軌跡：每筆交易對應一個餘額快照，異常時可精準定位導致差異的具體交易，便於對帳與除錯。

現代寫法補充：視窗函數

SQL Server 2012+ 可使用 SUM() OVER (PARTITION BY AccountNumber ORDER BY TransactionDateTime ROWS UNBOUNDED PRECEDING)，執行計畫更優、語法更簡潔，建議優先採用。