【LeetCode】185. 部门工资前三高的所有员工

0 问题描述

表: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id是该表的主键列(具有唯一值的列)。departmentId 是 Department 表中 ID 的外键（reference 列）。该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
 

表: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id 是该表的主键列(具有唯一值的列)。该表的每一行表示部门ID和部门名。
 

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。编写解决方案，找出每个部门中收入高的员工 。以任意顺序返回结果表。返回结果格式如下所示。

 

示例 1:

输入: 
Employee 表:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

Department  表:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+


输出: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

解释:
在IT部门:
- Max的工资最高
- 兰迪和乔都赚取第二高的独特的薪水
- 威尔的薪水是第三高的

在销售部:
- 亨利的工资最高
- 山姆的薪水第二高
- 没有第三高的工资，因为只有两名员工

1 数据准备

CREATE TABLE Department (
    `id` int     comment '',
    `name` string comment ''
) COMMENT 'xxx'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
 
insert overwrite table Department
values ('1', 'IT'),
       ('2', 'Sales');
 
CREATE TABLE Employee (
    `id` int     comment '',
    `name` string comment '',
    salary   int,
    departmentId int
) COMMENT 'xxx'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
 
insert overwrite table Employee
values ('1', 'Joe', '70000', '1'),
      ('2', 'Jim', '90000', '1'),
      ('3', 'Henry', '80000', '2'),
      ('4', 'Sam', '60000', '2'),
      ('5', 'Max', '90000', '1'),
      ('6', 'Janet', '69000', '1'),
      ('7', 'Randy', '85000', '1');

2 思路分析

思路一：开窗函数

select
       d.name    as `Department`,
       t1.name   as `Employee`,
       t1.salary as `salary`
from (select e.id,
             e.name,
             e.salary,
             e.departmentId,
             dense_rank() over (partition by e.departmentId order by e.salary desc) dr
      from Employee e) t1
-- 先开窗排序，再关联子表
left join department d on t1.departmentId = d.id
 where t1.dr <= 3;

思路二：常规做法

-- MySQL语法
select
	d.NAME AS Department,
	e.NAME AS Employee,
	e.salary AS Salary
from 
	employee e
	left join department d ON e.departmentId = d.id
where e.id in
 (
	select
		 e1.id
	from employee e1 join  employee e2
	on e1.departmentId = e2.departmentId
	where e1.salary <= e2.salary
	group by e1.id
	having count( DISTINCT e2.salary ) <= 3
 )

    思路分析：对于这种分组内取前几名的问题，mysql中可以先group by然后用having count()来筛选。具体到该题中，求每个部门的工资前三名，先在子查询中用Employee和自己做连接，连接条件是【部门相同但是工资比本人高】，那么接下来按照having count(Salary) <= 2来筛选的原理是：如果【跟本人一个部门而且工资比本人高的人数】不超过2个，那么本人一定是部门工资前三，这样内层查询可以查询出所有符合要求的员工ID，接下来外层查询就OK了。

3 总结