如何在按日期统计收支总额的SQL查询中同时获取每日最新余额

实现方案 #

你可以通过CTE（公共表表达式）分别计算每日的收支汇总、每日最后一笔交易的余额，再按日期关联两个结果即可，SQL 代码如下（支持MySQL 8.0+/PostgreSQL/SQL Server等所有支持窗口函数的主流数据库）：

WITH daily_total AS (
    -- 按天统计总支出、总存入
    SELECT
        DATE(movementdate) AS `Date`,
        SUM(expense) AS TotalExpense,
        SUM(deposit) AS TotalDeposit
    FROM movements
    GROUP BY DATE(movementdate)
),
daily_last_balance AS (
    -- 筛选每日最后一笔交易的余额
    SELECT
        DATE(movementdate) AS `Date`,
        balance AS Balance
    FROM (
        SELECT
            movementdate,
            balance,
            -- 给当日所有交易按时间倒序排序，排名1的就是最后一笔
            ROW_NUMBER() OVER(PARTITION BY DATE(movementdate) ORDER BY movementdate DESC) AS rn
        FROM movements
    ) t
    WHERE rn = 1
)
-- 关联两个结果得到最终输出
SELECT
    a.`Date`,
    a.TotalExpense,
    a.TotalDeposit,
    b.Balance
FROM daily_total a
INNER JOIN daily_last_balance b ON a.`Date` = b.`Date`
ORDER BY a.`Date`;

如果你使用的是不支持窗口函数的低版本数据库，也可以用关联子查询实现：

SELECT
    DATE(m.movementdate) AS `Date`,
    SUM(m.expense) AS TotalExpense,
    SUM(m.deposit) AS TotalDeposit,
    (
        SELECT balance 
        FROM movements m2 
        WHERE DATE(m2.movementdate) = DATE(m.movementdate) 
        ORDER BY m2.movementdate DESC 
        LIMIT 1
    ) AS Balance
FROM movements m
GROUP BY DATE(m.movementdate)
ORDER BY DATE(m.movementdate);

两种写法返回的结果都和你给出的预期输出完全一致。

内容的提问来源于stack exchange，提问作者J.Carlos