请求提供计算余额列的SQL查询语句（含表结构及示例数据）

要实现你需要的逐笔余额计算+汇总效果，我们可以结合窗口函数实现累计余额计算，再通过合并查询添加汇总行。下面是针对你的表结构和需求的具体实现：

1. 核心查询逻辑（带实时余额计算） #

首先关联transactions和accounts表，利用窗口函数按账户分组、交易日期排序，逐笔计算每笔交易后的实时余额：

WITH transaction_balances AS (
    SELECT
        t.ref_no,
        t.accounting_dt,
        -- 提取借方金额：D类型交易取amt，否则为0
        CASE WHEN t.debit_credit = 'D' THEN t.amt ELSE 0 END AS debit_amt,
        -- 提取贷方金额：C类型交易取amt，否则为0
        CASE WHEN t.debit_credit = 'C' THEN t.amt ELSE 0 END AS credit_amt,
        -- 计算实时余额：初始余额 + 累计贷方 - 累计借方
        a.bal_on_a_day + 
        SUM(CASE WHEN t.debit_credit = 'C' THEN t.amt ELSE -t.amt END)
            OVER (PARTITION BY t.account_no ORDER BY t.accounting_dt) AS current_balance,
        a.bal_on_a_day AS initial_balance,
        t.debit_credit,
        t.amt,
        t.account_no
    FROM transactions t
    JOIN accounts a ON t.account_no = a.account_no
    -- 可选：如果只需要特定账户，添加WHERE条件，比如 WHERE a.account_no = '10555589'
)

2. 合并明细与汇总行 #

通过UNION ALL将交易明细和汇总统计合并，同时格式化余额的计算说明：

SELECT
    ref_no,
    accounting_dt,
    debit_amt,
    credit_amt,
    -- 格式化余额显示，带上计算过程
    CONCAT(current_balance, ' (',
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY account_no ORDER BY accounting_dt) = 1
            THEN initial_balance, ' ', CASE WHEN debit_credit = 'D' THEN '-' ELSE '+' END, ' ', amt
            ELSE LAG(current_balance) OVER (PARTITION BY account_no ORDER BY accounting_dt), ' ', CASE WHEN debit_credit = 'D' THEN '-' ELSE '+' END, ' ', amt
        END, ')'
    ) AS balance,
    initial_balance
FROM transaction_balances
UNION ALL
-- 添加汇总行，统计借方总额、贷方总额和最终余额
SELECT
    'Total dr,cr,clos bal' AS ref_no,
    '' AS accounting_dt,
    SUM(debit_amt) AS debit_amt,
    SUM(credit_amt) AS credit_amt,
    MAX(current_balance) AS final_balance,
    initial_balance
FROM transaction_balances
GROUP BY account_no, initial_balance
-- 确保汇总行排在最后，明细按日期排序
ORDER BY 
    account_no,
    CASE WHEN ref_no = 'Total dr,cr,clos bal' THEN 1 ELSE 0 END,
    accounting_dt;

关键细节说明 #

• 窗口函数作用：SUM() OVER (PARTITION BY t.account_no ORDER BY t.accounting_dt) 实现了按账户分组、交易时间顺序的累计余额计算，确保每笔交易的余额是基于之前所有交易的结果。
• 借方/贷方区分：用CASE语句将debit_credit字段转换为直观的借方、贷方金额，便于后续统计和展示。
• 余额计算逻辑：初始余额 + 累计贷方金额 - 累计借方金额，完全匹配你期望的逐笔加减逻辑。
• 汇总行实现：通过UNION ALL合并明细和汇总，用SUM统计借贷总额，MAX(current_balance)获取最终余额（因为最后一笔交易的余额就是期末余额）。

针对示例数据的输出效果 #

执行上述查询后，针对account_no=10555589的交易，会得到和你期望完全一致的结果：

额外提示 #

如果你的数据库支持ROLLUP（比如MySQL 8.0+、PostgreSQL、SQL Server），可以用ROLLUP替代UNION ALL实现汇总，代码会更简洁；另外要确保accounting_dt是日期类型，如果是字符串格式，需要先转换为日期再排序，避免排序错误。

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