如何实现预算分类账中发薪日余额结转的SQL查询？

数据表结构与数据 #

1. 收入表（Income Table） #

2. 交易表（Transaction Table） #

收入表的PayDate字段与交易表的AssignedPayDay字段关联，所有支出交易均关联至对应发薪日。

需求 #

统计每个发薪日的交易总额，计算收支差额并将余额结转至下一个发薪日。

当前实现的问题 #

现有SQL仅能统计每个发薪日的交易总额及当期收支差额，无法实现余额跨期结转：

Select i.PayDate, i.Amount
,SUM(t.Amount) as TotalTrans
,(i.Amount - sum(t.Amount)) as Balance
From Income i
LEFT JOIN Transactions t on t.AssignedPayDay = i.PayDate
Group by Paydate, i.Amount
ORDER BY PayDate

当前查询返回结果：

期望结果 #

实现余额结转后，后续发薪日的余额需包含上期结转的剩余金额，例如1/10/2025的余额计算逻辑为：3050.00 + 上期结转余额358.48 - 566.25 = 2842.23，最终结果如下：

解决方案 #

使用CTE先计算每个发薪日的基础统计数据，再通过窗口函数SUM() OVER(ORDER BY PayDate)实现余额的累计结转，同时用COALESCE处理无交易发薪日的NULL值问题：

WITH PayPeriodStats AS (
    SELECT 
        i.PayDate,
        i.Amount,
        COALESCE(SUM(t.Amount), 0) AS TotalTrans,
        (i.Amount - COALESCE(SUM(t.Amount), 0)) AS PeriodBalance
    FROM Income i
    LEFT JOIN Transactions t ON t.AssignedPayDay = i.PayDate
    GROUP BY i.PayDate, i.Amount
)
SELECT 
    PayDate,
    Amount,
    TotalTrans,
    SUM(PeriodBalance) OVER (ORDER BY PayDate) AS Balance
FROM PayPeriodStats
ORDER BY PayDate;

逻辑说明 #

1. PayPeriodStats CTE：计算每个发薪日的收入、交易总额，以及当期的收支差额（PeriodBalance）
2. 主查询：通过窗口函数对PeriodBalance进行累计求和，得到包含上期结转余额的最终余额，按发薪日排序确保结转顺序正确

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