如何基于variation_id获取每个分类账账户的最新记录

首先看你的需求：从Balance表中，为每个account筛选出对应最大variation_id的所有记录——从你给出的期望结果里account 101的部分来看，这应该是你的核心目标。

你的原SQL存在的问题 #

你写的查询有几个关键问题导致无法得到正确结果：

• 子查询里的where account=account完全是多余的，不会起到任何过滤作用；
• 关联时只匹配了a.account = b.account，但没有将a.variation_id和子查询计算出的最大ID关联，所以会返回该account的所有行，而不是仅对应最大variation_id的行；
• 字段名如果是VARIATION ID（带空格），需要用反引号或双引号包裹（比如`VARIATION ID`），否则会触发语法错误，建议统一用下划线命名（variation_id）。

正确的解法 #

方法1：使用窗口函数（推荐，简洁高效）

窗口函数是处理这类分组筛选问题的最优方案，它可以快速为每个account计算出最大的variation_id，再筛选匹配的行：

SELECT account, variation_id, amount
FROM (
    SELECT 
        account, 
        variation_id, 
        amount,
        MAX(variation_id) OVER (PARTITION BY account) AS max_var_id
    FROM balance
) t
WHERE variation_id = max_var_id;

这个查询会先给每个account分组，计算出该组的最大variation_id，然后只保留variation_id等于这个最大值的记录，完美匹配你期望结果里account 101的输出。

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

如果你的数据库不支持窗口函数（比如MySQL 5.7及以前版本），可以用分组子查询+关联的方式实现：

SELECT a.account, a.variation_id, a.amount
FROM balance a
INNER JOIN (
    SELECT account, MAX(variation_id) AS max_var_id
    FROM balance
    GROUP BY account
) b ON a.account = b.account AND a.variation_id = b.max_var_id;

这个方法先分组计算每个account的最大variation_id，再通过account和variation_id关联原表，得到对应的记录。

关于你期望结果中的account 110部分 #

注意到你给出的期望结果里，account 110同时保留了variation_id=3和variation_id=4的记录，这和“取最大variation_id”的逻辑不符（110的最大variation_id是4）。如果这不是笔误，你的需求可能是每个account+variation_id组合中取最大的amount，可以用下面的SQL实现：

SELECT account, variation_id, amount
FROM (
    SELECT 
        account, 
        variation_id, 
        amount,
        ROW_NUMBER() OVER (PARTITION BY account, variation_id ORDER BY amount DESC) AS rn
    FROM balance
) t
WHERE rn = 1;

这个查询会给每个account+variation_id组合按amount降序排序，取第一行（即该组合下金额最大的记录）。

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