184. 部门工资最高的员工
难度中等
SQL架构
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 查询,找出每个部门工资最高的员工。对于上述表,您的 SQL 查询应返回以下行(行的顺序无关紧要)。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解释:
Max 和 Jim 在 IT 部门的工资都是最高的,Henry 在销售部的工资最高。
题解:
1、连表查询
通过题目的描述我们不难看出这一题我们首先需要将两个表通过DepartmentID连接起来
sql代码:
select Department.Name as Department, Employee.Name as Employee,Salary
from Employee
left join Department
on Employee.DepartmentId = Department.Id
运行结果如下表:
Department | Employee | Salary |
---|---|---|
IT | Joe | 70000 |
IT | Jim | 90000 |
Sales | Henry | 80000 |
Sales | Sam | 60000 |
IT | Max | 90000 |
接下来我们通过题目意思我们可以得知,只要工资为部门最高的员工信息都需要展示出来,所以这里我们不能用max()聚合函数当查询的结果,我们需要将max()当成判断的条件,当该部门的员工的工资等于最高工资时我们就展示改员工的信息。
2、构建条件
通过分组查询查询出每个部门的最高工资。
select max(Salary),DepartmentId
from Employee
group by DepartmentId
运行结果:
max(Salary) | DepartmentId |
---|---|
90000 | 1 |
80000 | 2 |
这里我们可以使用 in条件来进行判断,当departmentid和max(Salary)都满足时就展示改员工信息。
3、代码实现
sql代码
select Department.Name as Department, Employee.Name as Employee,Salary
from Employee
left join Department
on Employee.DepartmentId = Department.Id
where
(Salary,Department.Id)
in
(
select max(Salary),DepartmentId
from Employee
group by DepartmentId
)
这里使用了in()中传递两个参数,意思就是只有满足这两个条件全部满足时才可以满足条件。
运行结果:
Department | Employee | Salary |
---|---|---|
IT | Max | 90000 |
IT | Jim | 90000 |
Sales | Henry | 80000 |