按部门分组获取最高评分记录的正确SQL查询方法

问题背景 #

有一张名为mytable的表，包含name、department、rating三个字段，示例数据如下：

a, d1, 3
b, d1, 5
c, d1, 10
a1, d2, 4
a2, d2, 1
a3, d2, 5

需求是按department分组，取出每个部门中rating最高的记录，期望输出：

c, d1, 10
a3, d2, 5

尝试的查询语句存在错误：

select name, department, max(rating) from mytab
group by department;

错误原因是未将name纳入group by子句，但直接加入group by会导致每个单独的记录都被分组，无法得到预期结果。

解决方法 #

方法1：子查询关联法 #

先查询每个部门的最高评分，再关联原表获取对应记录：

SELECT t.name, t.department, t.rating
FROM mytable t
INNER JOIN (
    SELECT department, MAX(rating) AS max_rating
    FROM mytable
    GROUP BY department
) dept_max ON t.department = dept_max.department AND t.rating = dept_max.max_rating;

该方法适配所有支持基础SQL语法的数据库，逻辑直观：先提取各部门最高分，再匹配原表中对应部门且评分等于最高分的记录。

方法2：窗口函数法（适用于支持窗口函数的数据库） #

如果你的数据库支持窗口函数（如MySQL 8.0+、PostgreSQL、SQL Server等），可以用ROW_NUMBER()或RANK()实现：

SELECT name, department, rating
FROM (
    SELECT 
        name, 
        department, 
        rating,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY rating DESC) AS rn
    FROM mytable
) ranked
WHERE rn = 1;

• 若同一部门存在多条相同最高分记录，ROW_NUMBER()会随机返回一条；若想保留所有最高分记录，替换为RANK()即可：

SELECT name, department, rating
FROM (
    SELECT 
        name, 
        department, 
        rating,
        RANK() OVER (PARTITION BY department ORDER BY rating DESC) AS rn
    FROM mytable
) ranked
WHERE rn = 1;

窗口函数写法更灵活，适合复杂排序、分组场景。

方法3：关联子查询法 #

部分数据库支持在WHERE子句中使用关联子查询，写法更简洁：

SELECT name, department, rating
FROM mytable t
WHERE rating = (
    SELECT MAX(rating)
    FROM mytable
    WHERE department = t.department
);

逻辑为：对每条记录，检查其评分是否等于所在部门的最高分，符合条件则被选中。

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