MySQL分组取MAX(transaction_id)时对应balance字段不匹配如何解决

你原来的语句出错的核心原因是：在非严格SQL_MODE的MySQL环境中，GROUP BY 后直接查询未参与分组、也未被聚合函数包裹的balance字段时，数据库会从每个username分组的所有行里随意返回一个balance值，不会自动和你取到的MAX(transaction_id)做行匹配。

提示：你可以开启MySQL的ONLY_FULL_GROUP_BY SQL_MODE，避免这类分组后取值不符合预期的隐性问题，开启后不符合分组规范的SQL会直接执行报错。

方案1：MySQL 8.0及以上版本（推荐，性能更优） #

使用ROW_NUMBER()窗口函数按用户分组，按transaction_id倒序排序，取每个分组排序第一的行即可：

SELECT transaction_id, username, balance
FROM (
    SELECT 
        transaction_id, 
        username, 
        balance,
        ROW_NUMBER() OVER (PARTITION BY username ORDER BY transaction_id DESC) AS rn
    FROM user_transactions
) t
WHERE rn = 1;

如果同一个用户存在多个相同最大transaction_id的行，想全部返回的话可以把ROW_NUMBER()换成RANK()。

方案2：MySQL 5.x及更低版本 #

用子查询先拿到每个用户的最大transaction_id，再和原表关联匹配对应的行：

SELECT ut.transaction_id, ut.username, ut.balance
FROM user_transactions ut
INNER JOIN (
    SELECT username, MAX(transaction_id) AS max_tid
    FROM user_transactions
    GROUP BY username
) t ON ut.username = t.username AND ut.transaction_id = t.max_tid;

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