184. 部门工资最高的员工
注意:本题于中文版领扣上题目中表信息有误,且与SQL架构不一致,因此我将题目改为英文原版题目内容,可参考:184. Department Highest Salary
题目:
Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门工资最高的员工。(行顺序无要求)
例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
SQL架构:
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Jim', '90000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Henry', '80000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Sam', '60000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Max', '90000', '1');
Truncate table Department;
insert into Department (Id, Name) values ('1', 'IT');
insert into Department (Id, Name) values ('2', 'Sales');
解题语句:
方法一:三表联查
SELECT
d.Name AS Department,
e1.Name AS Employee,
e1.Salary
FROM
Employee AS e1
INNER JOIN Department AS d ON e1.DepartmentId = d.Id
INNER JOIN ( SELECT DepartmentId, MAX( Salary ) AS m FROM Employee GROUP BY DepartmentId ) AS e2 ON e1.Salary = e2.m
AND e1.DepartmentId = e2.DepartmentId;
注意:
三表联查时,将前两个表先合并,然后再与第三表连接比三表合并查询更快,即把前两表连接的 ON 语句位置提前。
(以下语句为三表同时连接,效率偏低,不推荐)
# 三表同时连接,效率偏低
SELECT
d.Name AS Department,
e1.Name AS Employee,
e1.Salary
FROM
Employee AS e1
JOIN ( SELECT DepartmentId, MAX( Salary ) AS m FROM Employee GROUP BY DepartmentId ) AS e2
JOIN Department d ON e1.Salary = e2.m
AND e1.DepartmentId = e2.DepartmentId
AND e1.DepartmentId = d.Id;
方法二:跨表查询 + 行级子查询
SELECT
d.Name AS Department,
e.Name AS Employee,
Salary
FROM
Employee AS e
JOIN Department AS d ON e.DepartmentId = d.Id
WHERE
( e.DepartmentId, Salary ) IN ( SELECT DepartmentId, MAX( Salary ) FROM Employee GROUP BY DepartmentId );