###这题想法挺简单的,
###1,先组内排序叫做rankx,这里提供了两种组内排序,一个是mysql8.0之后提供的rank() over()函数,一种试不用系统的高级函数。
###2,将部门名字连接到员工表,过滤rk<=3,并排序。
###注意的是这里的前三的排名是指,1,2,2,3,4这种。前三可以是3个以上的人数。所以使用了dense_rank。
with rankx as(
select *, dense_rank() over(partition by DepartmentId order by Salary desc) 'rk'from employee)
-- with rankx as(
-- select b.*,
-- (select count(distinct a.Salary) from Employee a where a.Salary >=b.Salary and a.DepartmentId=b.DepartmentId)'rk'
-- from Employee b
-- )
select d.name Department,r.name Employee ,r.Salary
from Department d join rankx r on d.id = r.DepartmentId
where r.rk<=3
order by DepartmentId ,r.Salary desc
mysql 力扣,185. 部门工资前三高的所有员工
最新推荐文章于 2024-08-13 11:47:25 发布