MySQL查询：获取员工详情及对应部门最高EMP_RATING

问题描述 #

需要查询员工详情（EMP_ID、FIRST_NAME、LAST_NAME、ROLE、DEPT、EMP_RATING），同时在每条记录中展示该员工的EMP_RATING以及其所属部门的最高EMP_RATING。

原SQL的问题 #

你当前的SQL语句通过GROUP BY dept, emp_id, first_name, last_name, role , emp_rating进行分组，由于emp_id是唯一标识员工的字段，每个分组实际上只包含单个员工的记录，因此MAX(emp_rating)计算出的结果就是该员工自身的评分，无法得到部门级别的最高评分，这就是输出不符合预期的原因。

正确解法 #

方法1：使用窗口函数（MySQL 8.0及以上版本推荐） #

窗口函数MAX() OVER (PARTITION BY dept)可以在不分组聚合的前提下，为每条记录计算其所属部门的最高评分：

SELECT 
    emp_id, 
    first_name, 
    last_name, 
    role, 
    dept, 
    emp_rating,
    MAX(emp_rating) OVER (PARTITION BY dept) AS MAX_EMP_RATING
FROM emp_record_table;

• PARTITION BY dept表示按部门划分窗口，对每个部门单独计算最高评分
• 这条语句会保留所有员工的原始记录，同时为每条记录追加对应部门的最高评分

方法2：使用子查询关联（兼容MySQL 5.x版本） #

如果你的MySQL版本不支持窗口函数，可以通过子查询先计算各部门的最高评分，再与原表关联：

SELECT 
    e.emp_id, 
    e.first_name, 
    e.last_name, 
    e.role, 
    e.dept, 
    e.emp_rating,
    d.MAX_EMP_RATING
FROM emp_record_table e
JOIN (
    SELECT dept, MAX(emp_rating) AS MAX_EMP_RATING
    FROM emp_record_table
    GROUP BY dept
) d ON e.dept = d.dept;

• 子查询d先统计每个部门的最高评分，再通过dept字段与原表关联，将部门最高评分匹配到每条员工记录上

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