如何用SQL查询每个部门工资最高的员工姓名？

Question

如何用SQL查询每个部门工资最高的员工姓名？ 在实际项目中，我们常需找出每个部门工资最高的员工。假设有一张员工表`employees`，包含字段`department_id`（部门ID）、`name`（员工姓名）和`salary`（工资）。一种常见做法是使用子查询与`GROUP BY`结合。先按部门分组获取最高工资，再关联原表获取对应姓名。示例如下： ```sql SELECT e.department_id, e.name, e.salary FROM employees e JOIN ( SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id ) sub ON e.department_id = sub.department_id AND e.salary = sub.max_salary; ``` 注意：若多个员工工资相同且均为最高，此方法会列出所有符合条件的员工。如何处理这种多值情况，取决于具体业务需求。

Answer 1

1. 问题背景与初步理解

在数据库操作中，查询每个部门工资最高的员工是一个常见的需求。假设我们有一张名为`employees`的表，包含以下字段：

• `department_id`：部门ID
• `name`：员工姓名
• `salary`：工资

我们需要通过SQL语句实现按部门分组，并找出每个部门中工资最高的员工。这里需要注意的是，如果一个部门中有多个员工的工资相同且都是最高工资，我们需要根据业务需求决定是否列出所有这些员工。

SELECT e.department_id, e.name, e.salary
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) sub ON e.department_id = sub.department_id AND e.salary = sub.max_salary;
    

2. 技术分析与解决方案

为了解决这个问题，我们可以从以下几个角度进行分析和解决：

1. 使用子查询结合`GROUP BY`获取每个部门的最高工资。
2. 通过主表与子查询结果关联，筛选出符合条件的员工信息。
3. 考虑多值情况（即多个员工工资相同且为最高），并根据业务需求调整查询逻辑。

2.1 子查询与主表关联

子查询部分首先按`department_id`分组，并计算出每个部门的最高工资`MAX(salary)`。然后将子查询的结果与主表`employees`进行连接，条件是部门ID相等且工资等于该部门的最高工资。

department_id	name	salary
1	Alice	5000
1	Bob	6000
2	Charlie	7000
2	Diana	7000
3	Eve	8000

2.2 处理多值情况

如果一个部门有多个员工的工资相同且均为最高工资，上述查询会返回所有这些员工的信息。如果业务需求只允许返回一个员工，则可以通过添加额外的排序规则来限制结果数量，例如按员工姓名或ID排序后取第一个。

SELECT e.department_id, e.name, e.salary
FROM employees e
WHERE (e.department_id, e.salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);
    

3. 进阶优化与扩展

对于大规模数据集，上述方法可能会导致性能瓶颈。可以考虑以下优化方式：

• 使用窗口函数`ROW_NUMBER()`或`RANK()`对数据进行排序和标记。
• 创建索引以加速查询过程。

3.1 窗口函数示例

使用窗口函数`ROW_NUMBER()`可以为每个部门的员工按工资降序排列，并标记每行的顺序号。然后筛选出每个部门的第一行即可。

WITH RankedEmployees AS (
    SELECT 
        department_id, 
        name, 
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT department_id, name, salary
FROM RankedEmployees
WHERE rn = 1;
    

3.2 性能优化建议

在实际项目中，为了提高查询效率，可以为`employees`表的`department_id`和`salary`字段创建复合索引。这样可以显著减少查询时的扫描范围。

CREATE INDEX idx_department_salary ON employees(department_id, salary);
    

流程图示例