LeetCode-SQL-184-部门工资最高的员工
大家好，我是Peter~
本文讲解的是LeetCode-SQL的第184题目，题目名为：部门工资最高的员工
难易程度：中等
题目
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
代码语言：javascript复制 ---- ------- -------- --------------
| 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 表包含公司所有部门的信息
代码语言：javascript复制 ---- ----------
| Id | Name |
---- ----------
| 1 | IT |
| 2 | Sales |
---- ---------- 编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）
代码语言：javascript复制 ------------ ---------- --------
| Department | Employee | Salary |
------------ ---------- --------
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
------------ ---------- -------- 解释：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。也就是说：如果部门中存在多个人同时最高，都要显示出来。
思路
个人思路1
个人思路：找到每个部门中的最高值，再和这个最高值进行计较；如果大于等于这个最高值，那肯定是部门最高的。
代码语言：javascript复制select
d.Name Department
,e.Name Employee
,e.Salary Salary
from Employee e , Department d
where e.DepartmentId = d.Id -- 在同一个部门中进行比计较
and e.Salary >= (select max(Salary) from Employee where DepartmentId=d.Id); -- 找出每个部门的最高值；如果大于等于这个最高值，肯定是最高的上面子句的作用就是找到每个部门中的薪水最高值
个人思路2
思路2是先使用窗口函数根据每个部门进行排序，从而得到每个人的排名，我们再取出每个人的名次即可。
⚠️：窗口函数在Hive或者MySQL8.X才有！！！
代码语言：javascript复制-- 每个部门最高
SELECT S.NAME, S.EMPLOYEE, S.SALARY
FROM (SELECT
d.Name,
e.Name Employee,
e.Salary,
dense_rank() OVER(PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) number -- 根据部门分区，薪水排序
FROM Employee e
LEFT JOIN Department d
ON e.DepartmentId = d.Id) S
WHERE S.number = 1因为薪水可能有重复的，所以员工的排名可能有相同的，因此使用rank()或者dense_rank()比较适合。
通过上面的思路，我们可以变化很多花样，取出不同名次的员工：
1、取出排名前2名的员工：
代码语言：javascript复制-- 每个部门最高
SELECT S.NAME, S.EMPLOYEE, S.SALARY
FROM (SELECT
d.Name,
e.Name Employee,
e.Salary,
dense_rank() OVER(PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) number -- 根据部门分区，薪水排序
FROM Employee e
LEFT JOIN Department d
ON e.DepartmentId = d.Id) S
WHERE S.number <= 2; -- 排名前22、取出第一个名或者第3名的员工
代码语言：javascript复制-- 每个部门最高
SELECT S.NAME, S.EMPLOYEE, S.SALARY
FROM (SELECT
d.Name,
e.Name Employee,
e.Salary,
dense_rank() OVER(PARTITION BY e.DepartmentId ORDER BY e.Salary DESC) number -- 根据部门分区，薪水排序
FROM Employee e
LEFT JOIN Department d
ON e.DepartmentId = d.Id) S
WHERE S.number = 1 or S.number =3; -- 排名第一或者第三官方题解
官方题解中给的思路：通过两个表的直接关联，再通过in关键词的多个字段的包含关系的使用，这种in关键词前面带有多个字段的写法还是学习啦！
代码语言：javascript复制SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (Employee.DepartmentId , Salary) IN ( -- 两个字段同时使用
SELECT
DepartmentId, -- 部门分组找出部门号和薪水的最大值
MAX(Salary)
FROM Employee
GROUP BY DepartmentId
)参考思路1
有位作者的思路和官方给定的思路是比较类似的：
1、根据部门分组找出最高薪水
先对 DepartmentId 字段分组查询最大值，得到不同 DepartmentId 下的最大值
代码语言：javascript复制select -- 根据部门找分组找到部门号和最大值
DepartmentId,
max(Salary) as max_sal
from Employee
group by DepartmentId
-- 结果
DepartmentId max_sal
1 9000
2 80002、把步骤1中的结果当做临时表，和原来的Employee表进行关联
代码语言：javascript复制select
a.Id, -- 员工号
a.Name as Employee, -- 员工姓名
a.Salary, -- 员工薪水
a.DepartmentId -- 部门id
from Employee a -- 和原来的Employee进行关联
left join (
select DepartmentId, max(Salary) as max_sal
from Employee
group by DepartmentId) b -- b就是步骤1得到的临时表
on a.DepartmentId = b.DepartmentId and a.Salary = b.max_sal3、从步骤2的临时结果和部门Department表相关联，取出我们想要的字段；
代码语言：javascript复制select
bb.Name as Department,
aa.Employee,
aa.Salary
from (
select -- 2、步骤2的结果
a.Id,
a.Name as Employee,
a.Salary,
a.DepartmentId
from Employee a
join (
select -- 1、步骤1的结果
DepartmentId,
max(Salary) as max_sal
from Employee
group by DepartmentId
) b on a.DepartmentId = b.DepartmentId and a.Salary = b.max_sal) aa
join Department bb on aa.DepartmentId = bb.Id; -- 3、和部门表的关联，取出想要的字段参考思路2
通过谓词exists的使用：部门工资最高等价于不存在
代码语言：javascript复制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 -- 部门分组
WHERE NOT EXISTS (SELECT * -- 不存在E1中的薪水小于E2中的薪水，说明E1中的就是最高的
FROM Employee AS E2
WHERE E1.DepartmentId = E2.DepartmentId
AND E1.Salary < E2.Salary) -- 这里不同带上等号，薪水可能存在重复的情况
