数据库 leetcode 183

select 
b.name as Department,a.name as Employee,a.Salary
from Employee a join Department b on a.DepartmentId=b.id 
and (a.salary,a.DepartmentId) in (select max(salary),DepartmentId from Employee group by DepartmentId)

经典的topn
select Department, Employee,Salary
from(
    select d.name as Department,e.name as Employee,e.Salary as Salary,
    dense_rank() over (partition by DepartmentId order by Salary desc) as rk
    from Employee e join Department d on e.DepartmentId=d.id
) as a
where rk<=3

【本题考点】

1）考察如何使用窗口函数及专用窗口函数排名的区别：rank, dense_rank, row_number
2）经典topN问题：每组最大的N条记录。这类问题涉及到“既要分组，又要排序”的情况，要能想到用窗口函数来实现。

topN问题 sql模板

select *
from (
   select *, 
          row_number() over (partition by 要分组的列名
                       order by 要排序的列名 desc) as 排名
   from 表名) as a
where 排名 <= N;