LeetCode之部门工资前三高的员工

有表
department

Employee

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

±-----------±---------±-------+
| Department | Employee | Salary |
±-----------±---------±-------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
±-----------±---------±-------+

我的答案:
select
d.Name as Department,
e.Name as Employee,
e.Salary as Salary
from
Employee e inner join Department d
on e.departmentId=d.id
where
(
select count(distinct e2.salary)
from employee as e2
where e.departmentId=e2.departmentId
and
e.salary<=e2.salary
) <=3
order by e.departmentId,e.salary desc

涉及到的知识点:
(1)inner join通过on 条件联系两个表，如果遇到不符合条件的on则不符合条件的那一行将不会被显示(这是和left join的区别)
(2)order by 多个字段时，会从前往后依次根据需要排序的字段进行排序。
例如 order by e.departmentId,e.salary desc
含义是先根据departmentId排序，然后在departmentId排序后的每个小分组中对salary进行子排序

语句逻辑:
(1)根据部门id内联 Employee 和 Department ：
Employee e inner join Department d
on e.departmentId=d.id
(2)针对每一条内联后的数据，我们对其进行子查询判断是否在其部门工资排名前三：
(
select count(distinct e2.salary)
from employee as e2
where e.departmentId=e2.departmentId
and
e.salary<=e2.salary #子查询条件
) <=3 #某条记录在子查询中工资数大于自己的数量不超过三个就是排名前三
(3)给部门分组，并对每个小组进行排序
order by e.departmentId,e.salary desc