SQL GROUP BY分组取最大值时返回所有表列的高效查询方法

原有写法出错原因 #

你写的关联查询只匹配了grade字段，没有匹配最高分数，所以会返回同分组下的所有行，自然无法得到正确结果。

最高效实现方案 #

1. 支持窗口函数的数据库（优先推荐，仅需扫描一次表）

主流新版本数据库（MySQL 8.0+、PostgreSQL、Oracle、SQL Server等）都支持窗口函数，该方案性能最优：

SELECT id, code, marks, grade
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY grade ORDER BY marks DESC) AS rank_num
    FROM `table`
) t
WHERE rank_num = 1;

如果同一分组下有多行marks等于最大值，需要全部返回的话，把ROW_NUMBER()替换为RANK()即可。

2. 不支持窗口函数的低版本数据库

修正原有关联逻辑，增加marks匹配条件即可，只要给grade和marks建立联合索引，性能足够满足大部分场景：

SELECT t.*
FROM `table` t
INNER JOIN (
    SELECT grade, MAX(marks) AS max_marks
    FROM `table`
    GROUP BY grade
) a ON t.grade = a.grade AND t.marks = a.max_marks;

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