力扣-部门工资最高的员工

大家好，我是空空star，本篇带你了解一道简单的力扣sql练习题。

---

前言

---

一、题目：184. 部门工资最高的员工

表： Employee

+--------------+---------+
| 列名          | 类型    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id是此表的主键列。
departmentId是Department表中ID的外键。
此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。

表： Department

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id是此表的主键列。
此表的每一行都表示一个部门的ID及其名称。

编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。

二、解题

1.正确示范①

提交SQL

先把每个部门的最高工资计算出来，再通过员工表的部门id和工资分别与计算出最高工资的数据集中的部门id和最高工资关联，取到想要的数据。

select u3.name Department,
u1.name Employee,
u1.salary Salary
from Employee u1
join(
select departmentId,max(salary) max_salary
from Employee
group by departmentId
) u2
on u1.departmentId=u2.departmentId
and u1.salary=u2.max_salary
join Department u3
on u1.departmentId=u3.id

或者

select u3.name Department,
u1.name Employee,
u1.salary Salary
from Employee u1
join Department u3
on u1.departmentId=u3.id
where (u1.departmentId,u1.salary) in(
select departmentId,max(salary) max_salary
from Employee
group by departmentId
)

运行结果

2.正确示范②

提交SQL

使用rank() over(partition by departmentId order by salary desc)
取排名是1的，因为rank是并列排序，这样每个部门中最高工资一样的都会保留。

select u2.name Department,
u1.name Employee,
u1.salary Salary
from(
select id,name,
salary,
departmentId,
rank() over(partition by departmentId order by salary desc ) col
from Employee
) u1
left join Department u2
on u1.departmentId=u2.id
where col=1
order by u1.id

运行结果

3.正确示范③

提交SQL

使用dense_rank() over(partition by departmentId order by salary desc)
取排名是1的，因为dense_rank是并列排序，这样每个部门中最高工资一样的都会保留。

select u2.name Department,
u1.name Employee,
u1.salary Salary
from(
select id,name,
salary,
departmentId,
dense_rank() over(partition by departmentId order by salary desc ) col
from Employee
) u1
left join Department u2
on u1.departmentId=u2.id
where col=1
order by u1.id

运行结果

4.其他

---

总结

正确示范①思路：
select departmentId,max(salary) max_salary
from Employee
group by departmentId
正确示范②思路：
rank() over(partition by departmentId order by salary desc)
取排名1
正确示范③思路：
dense_rank() over(partition by departmentId order by salary desc)
取排名1