MySQL 8.x中GROUP BY选取非分组列匹配最大值的方案

关于第一个疑问 #

你的认知完全正确。
你最初写的SELECT race, name, MAX(age) FROM animal GROUP BY race从SQL执行逻辑上就不可能稳定得到预期结果：

• 该语句仅按race字段分组，name字段既没有加入GROUP BY分组维度，也没有被聚合函数包裹，属于SQL标准中禁止的写法，MySQL在未严格开启ONLY_FULL_GROUP_BY时虽然能执行，但返回的name值是分组扫描过程中取到的任意值，和同组的MAX(age)没有绑定关系，结果完全不可控，哪怕某次运行返回了正确值也属于巧合。

关于第二个疑问 #

你使用的MySQL 8.x支持窗口函数，这是比关联子查询更简洁、性能更好的实现方案，代码如下：

SELECT race, name, age AS max_age
FROM (
  SELECT
    race,
    name,
    age,
    ROW_NUMBER() OVER (PARTITION BY race ORDER BY age DESC) AS age_rank
  FROM animal
) t
WHERE age_rank = 1;

逻辑说明 #

• PARTITION BY race实现按种族分组，和你原来的GROUP BY race分组逻辑一致
• ORDER BY age DESC让每个分组内的行按年龄从高到低排序
• ROW_NUMBER()会给每个分组内的行按排序结果生成从1开始的序号，序号为1的行就是分组内年龄最大的行，最后过滤序号为1的结果即可。

补充说明：如果同一种族下存在多个动物年龄并列最大的场景，需要把所有并列最大的结果都返回时，将ROW_NUMBER()替换为RANK()即可。

性能对比 #

你之前写的关联子查询属于相关子查询，外层表每扫描一行就要执行一次子查询计算对应种族的最大年龄，数据量大时性能很差；窗口函数写法仅需对表做一次扫描排序，执行效率远高于关联子查询。

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