如何按用户及财年获取最新余额及对应更新日期

我有一张名为balances的表，需要获取每个用户在每个财年（financial_year）的最新余额（balance）及其对应的更新日期（date_updated）。

表结构与数据 #

期望输出 #

问题所在 #

我尝试了以下SQL语句，但发现用max()函数跨多列查询无法得到正确结果：

SELECT name, max(balance), financial_year, max(date_updated)
FROM balances
group by name, financial_year

这是因为max(balance)和max(date_updated)可能来自表中不同的行，并不是同一个最新记录的对应值。

解决方案 #

方法1：使用窗口函数ROW_NUMBER()（推荐，适用于大多数现代数据库） #

通过窗口函数按用户和财年分组，对每个分组内的记录按date_updated倒序排序，取排序为1的记录（即最新的那条）：

SELECT name, balance, financial_year, date_updated
FROM (
    SELECT 
        name, 
        balance, 
        financial_year, 
        date_updated,
        ROW_NUMBER() OVER (PARTITION BY name, financial_year ORDER BY date_updated DESC) AS rn
    FROM balances
) t
WHERE rn = 1;

方法2：子查询关联（兼容旧版数据库） #

先找出每个用户+财年组合的最新更新日期，再关联原表获取对应的余额：

SELECT b.name, b.balance, b.financial_year, b.date_updated
FROM balances b
INNER JOIN (
    SELECT name, financial_year, MAX(date_updated) AS latest_date
    FROM balances
    GROUP BY name, financial_year
) t ON b.name = t.name 
    AND b.financial_year = t.financial_year 
    AND b.date_updated = t.latest_date;

方法3：使用CTE（更清晰的结构） #

和方法1逻辑一致，用CTE替代子查询：

WITH ranked_balances AS (
    SELECT 
        name, 
        balance, 
        financial_year, 
        date_updated,
        ROW_NUMBER() OVER (PARTITION BY name, financial_year ORDER BY date_updated DESC) AS rn
    FROM balances
)
SELECT name, balance, financial_year, date_updated
FROM ranked_balances
WHERE rn = 1;

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