LeetCode题库 数据库分类 困难 185. 部门工资前三高的员工 MS SQL解法 答案

最近在刷leetcode题库，刚好看到中文网站已经上线了，新增了ms sql解释器。困难难度链接在这，就顺便吐槽一下这个中文名字吧。 [ 力扣题库 ]

---

185. 部门工资前三高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。

Id	Name	Salary	DepartmentId
1	Joe	70000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1

Department 表包含公司所有部门的信息。

Id	Name
1	IT
2	Sales

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

Department	Employee	Salary
IT	Max	90000
IT	Randy	85000
IT	Joe	70000
Sales	Henry	80000
Sales	Sam	60000

解法

select department ,employee ,salary 
from (
select b.name as department ,a.name as employee ,salary
,dense_rank() over(partition by departmentid order by salary desc) as rn 
from employee a 
left join department b
on a.departmentid=b.id where b.name is not null) a
where rn<=3 
order by department,salary desc

---

*如有问题，敬请留言。