(LeetCode 185 ) 部门工资前三高的所有员工

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

+----+-------+--------+--------------+
| 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 |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：

IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

分析题目

要求：
1. 不同部门
2. 工资前三高

解题思路

需要根据部门进行分组，分组后对数据进行排序。
但是MySql 8.0 之前无法对数据进行分组，对分组后的数据进行排序
换一种思路，同一部门，比薪资高的薪资不超过3个，那么薪资就属于前三高
那么如果比较呢，where中关联表进行对比

解题步骤

1. where 中 比较，count()统计

SELECT
    c.`name` AS department,
    a.`name` AS employee,
    a.salary 
FROM
    employee a
    LEFT JOIN department c ON a.DepartmentId = c.id 
WHERE
    3 > (SELECT count(b.salary) FROM employee b WHERE a.DepartmentId = b.DepartmentId AND b.salary > a.salary)

image.png

2. distinct
   统计结果并不对，并不是工资前三高，而是工资高的前三个人

SELECT
    c.`name` AS department,
    a.`name` AS employee,
    a.salary 
FROM
    employee a
    LEFT JOIN department c ON a.DepartmentId = c.id 
WHERE
    3 > (SELECT count(b.salary) FROM employee b WHERE a.DepartmentId = b.DepartmentId AND b.salary > a.salary)

解法二：使用MySQL8的开窗函数 dense_rank()

select 
Department,Employee,Salary
from
(
select 
    d.name Department,e.name Employee,e.salary Salary,dense_rank()
    over(partition by d.name order by e.salary desc) rk
from
    Employee e left join Department d
    on e.departmentid = d.id
)t1
where rk <= 3