2020-11-14-Mysql(领扣-分组求值)

image.png

with x as 
(
select 
Name, Salary,DepartmentId,
dense_rank() over(partition by DepartmentId  order by Salary desc)  as result
from 
Employee) 
select 
Department.Name AS Department ,
x.Name AS   Employee,
x.Salary AS Salary
from
Department join x on  x.DepartmentId = Department.Id  
where x.result = 1

image.png

select s.name as department,t.name as Employee,t.salary from (
select name,salary,departmentid,
dense_rank() over(partition by departmentid order by salary desc) as rn 
from 
Employee) t
join department s on t.departmentid = s.id 
where rn <=3