MySQL中如何按departement_id分组获取每组最后一条员工记录？

背景说明 #

现有两张MySQL数据表：

• departements表结构及数据：

+----------------+------------------+------------+  
| departement_id | departement_name | manager_id |  
+----------------+------------------+------------+  
|             10 | Administration   |        101 |  
|             20 | IT               |        103 |  
+----------------+------------------+------------+  

• employees表结构及数据：

+-------------+--------+--------+------------+----------------+  
| employee_id | name   | salary | manager_id | departement_id |  
+-------------+--------+--------+------------+----------------+  
|         100 | Steven |   8000 |        101 |             10 |  
|         101 | Lexa   |  10000 |        101 |             10 |  
|         102 | Bruce  |   9000 |        103 |             20 |  
|         103 | Diana  |  11000 |        103 |             20 |  
|         104 | Bruce  |   8500 |        103 |             20 |  
+-------------+--------+--------+------------+----------------+

原查询仅按部门分组返回了每组第一条员工记录，无法满足获取各部门employee_id最大的员工+部门平均薪资的需求，以下是两种可行实现方法：

方法一：子查询统计后关联（兼容所有MySQL版本） #

先通过子计算每个部门的最大employee_id和平均薪资，再关联员工表匹配目标记录：

SELECT 
    e.employee_id, 
    e.name, 
    e.departement_id, 
    dept_stats.avg_salary
FROM 
    employees e
JOIN (
    SELECT 
        departement_id,
        MAX(employee_id) AS max_emp_id,
        AVG(salary) AS avg_salary
    FROM 
        employees
    GROUP BY 
        departement_id
) dept_stats ON e.departement_id = dept_stats.departement_id 
             AND e.employee_id = dept_stats.max_emp_id
JOIN departements d ON e.departement_id = d.departement_id;

逻辑说明 #

1. 子查询dept_stats按部门分组，计算每个部门的核心统计值；
2. 关联员工表，通过部门ID+最大员工ID定位目标员工；
3. 关联departements表（若无需部门额外信息，此步骤可省略）。

方法二：窗口函数实现（MySQL 8.0+版本适用） #

利用窗口函数无需分组即可计算部门平均薪资，同时给员工按employee_id降序排名，筛选排名第一的记录：

SELECT 
    employee_id,
    name,
    departement_id,
    avg_salary
FROM (
    SELECT 
        e.employee_id,
        e.name,
        e.departement_id,
        AVG(e.salary) OVER (PARTITION BY e.departement_id) AS avg_salary,
        ROW_NUMBER() OVER (PARTITION BY e.departement_id ORDER BY e.employee_id DESC) AS rn
    FROM 
        employees e
    JOIN departements d ON e.departement_id = d.departement_id
) t
WHERE rn = 1;

逻辑说明 #

1. AVG() OVER (PARTITION BY departement_id)：按部门分区计算平均薪资，无需全局分组；
2. ROW_NUMBER() OVER (...)：按部门分区，给员工按employee_id从大到小排名，最大员工排名为1；
3. 外层筛选排名为1的记录，即为目标结果。

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