Leetcode | 部门工资最高的员工

[techiall]

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

mysql

先用 group by 对 部门id 进行聚合，然后返回 最高的工资 和 对应的部门id。

select max(Salary) s, DepartmentId from Employee group by DepartmentId

给这张表起个别名，如 tmp，然后使用表连接即可。

连接条件为 tmp 表中的最高工资等于 Employee 中的 salary，剩下的列项按要求列出即可。

select b.Name Department, e.Name Employee, e.Salary Salary

from Employee e, Department b, (select max(Salary) s, DepartmentId from Employee group by DepartmentId) c

where e.DepartmentId = b.Id and e.Salary = c.s and e.DepartmentId = c.DepartmentId;