leetcode-for-sql-部门工资最高的员工

LeetCode-SQL-184-部门工资最高的员工

大家好，我是Peter~

本文讲解的是LeetCode-SQL的第184题目，题目名为：部门工资最高的员工

难易程度：中等

题目

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

个人思路：找到每个部门中的最高值，再和这个最高值进行计较；如果大于等于这个最高值，那肯定是部门最高的。

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才有！！！

-- 每个部门最高
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名的员工：

-- 每个部门最高
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;  -- 排名前2

2、取出第一个名或者第3名的员工

-- 每个部门最高
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关键词前面带有多个字段的写法还是学习啦！

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 下的最大值

select   -- 根据部门找分组找到部门号和最大值
	DepartmentId,
	max(Salary) as max_sal
from Employee
group by DepartmentId

-- 结果
DepartmentId  max_sal
1              9000
2              8000

2、把步骤1中的结果当做临时表，和原来的Employee表进行关联

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_sal

3、从步骤2的临时结果和部门Department表相关联，取出我们想要的字段；

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的使用：部门工资最高等价于不存在

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)  --  这里不同带上等号，薪水可能存在重复的情况