如何在SQL表中创建自动计算的账户累计余额列？

方案1：使用触发器维护余额列 #

先调整表结构，将Balance设为普通存储列，通过触发器在插入、更新交易时自动计算并维护余额：

建表语句 #

CREATE TABLE Transactions 
(
    TranID int PRIMARY KEY IDENTITY(1,1),
    [Date] date,
    Category varchar(50),
    Remarks varchar(500),
    Debit Decimal(10, 2) DEFAULT 0,  -- 支出（扣减余额）
    Credit Decimal(10, 2) DEFAULT 0, -- 收入（增加余额）
    Balance Decimal(10, 2) NOT NULL  -- 存储计算后的账户余额
)

插入触发器（自动计算新记录余额） #

CREATE TRIGGER trg_Transactions_InsertBalance
ON Transactions
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE t
    SET t.Balance = ISNULL(
        (SELECT TOP 1 Balance FROM Transactions WHERE TranID < inserted.TranID ORDER BY TranID DESC),
        0
    ) + inserted.Credit - inserted.Debit
    FROM Transactions t
    INNER JOIN inserted ON t.TranID = inserted.TranID;
END

• 第一条记录的余额直接基于Credit - Debit计算（比如期初存入100美元，就插入Credit=100, Debit=0，余额自动设为100）
• 后续记录自动取上一条的余额，加上当前收入、减去当前支出

更新触发器（修改历史记录后同步后续余额） #

如果修改某条交易的收支金额，后续所有记录的余额需要重新计算：

CREATE TRIGGER trg_Transactions_UpdateBalance
ON Transactions
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MinTranID int;
    SELECT @MinTranID = MIN(TranID) FROM inserted;

    WITH RecalcBalances AS (
        SELECT 
            TranID,
            Credit,
            Debit,
            ISNULL(
                (SELECT TOP 1 Balance FROM Transactions WHERE TranID < @MinTranID ORDER BY TranID DESC),
                0
            ) + Credit - Debit AS Balance
        FROM Transactions
        WHERE TranID = @MinTranID

        UNION ALL

        SELECT 
            t.TranID,
            t.Credit,
            t.Debit,
            rb.Balance + t.Credit - t.Debit AS Balance
        FROM Transactions t
        INNER JOIN RecalcBalances rb ON t.TranID = rb.TranID + 1
    )
    UPDATE t
    SET t.Balance = rb.Balance
    FROM Transactions t
    INNER JOIN RecalcBalances rb ON t.TranID = rb.TranID;
END

方案2：创建带余额计算的视图（推荐对接Power BI） #

不直接在表中存储余额，而是通过视图用窗口函数实时计算，避免数据冗余和维护成本：

基础交易表（无余额列） #

CREATE TABLE Transactions 
(
    TranID int PRIMARY KEY IDENTITY(1,1),
    [Date] date,
    Category varchar(50),
    Remarks varchar(500),
    Debit Decimal(10, 2) DEFAULT 0,
    Credit Decimal(10, 2) DEFAULT 0
)

带余额的视图 #

如果有单独的期初余额配置表：

-- 先创建期初余额配置表
CREATE TABLE AccountSettings
(
    AccountID int PRIMARY KEY,
    InitialBalance Decimal(10, 2) NOT NULL DEFAULT 0
)
-- 插入期初余额（示例：100美元）
INSERT INTO AccountSettings (AccountID, InitialBalance) VALUES (1, 100.00)

-- 创建视图
CREATE VIEW vw_TransactionsWithBalance
AS
SELECT 
    t.TranID,
    t.[Date],
    t.Category,
    t.Remarks,
    t.Debit,
    t.Credit,
    s.InitialBalance + SUM(t.Credit - t.Debit) OVER (
        ORDER BY t.TranID 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Balance
FROM Transactions t
CROSS JOIN AccountSettings s
WHERE s.AccountID = 1

Power BI直接连接该视图即可获取实时计算的余额，无需维护触发器。

方案3：在Power BI中用DAX计算余额（最适合仪表盘场景） #

将原始交易数据导入Power BI后，用DAX函数直接计算余额，把计算逻辑放在BI工具中：

创建计算列（每行的累计余额） #

Balance = 
VAR InitialBalance = 100.00  -- 可替换为从参数表获取的期初值
RETURN
InitialBalance + CALCULATE(
    SUM(Transactions[Credit]) - SUM(Transactions[Debit]),
    FILTER(
        ALL(Transactions),
        Transactions[TranID] <= EARLIER(Transactions[TranID])
    )
)

创建度量值（用于仪表盘实时余额展示） #

Current Balance = 
VAR InitialBalance = 100.00
RETURN
InitialBalance + SUM(Transactions[Credit]) - SUM(Transactions[Debit])

该方案无需修改数据库结构，且DAX支持灵活的筛选、分组，更适配Power BI仪表盘的交互需求。

内容的提问来源于stack exchange，提问作者Abhishek Pagare