一、解题思路
对不同的departmentId进行分组,在组间进行排序,并给与组间每行一个序号,每个新组别都以1开始。最后筛选出ranks小于3的行即满足要求。
二、常见排名函数
窗口函数:
window_function_name
OVER (
partition by <用于分组的列名>
order by <按序叠加的列名>
ROWS|RANGE <窗口滑动的数据范围> )
1、row_number
是一个窗口函数,为结果中的每一行提供一个编号。排序为:1,2,3,4
ROW_NUMBER() OVER (PARTITION BY column1,column2 ORDER BY column1 , column2, ...)
//若没有order by则按照结果集中的任意顺序编号
2、rank()
是一个窗口函数,排序如:1,2,2,4
3、dense_rank()
是一个窗口函数,排序如:1,2,2,3
三、题解
select
Department,Employee,Salary
from(
select
a2.name as Department,a1.name as Employee,a1.salary as Salary,dense_rank() over(partition by departmentId order by salary desc) as ranks
//对departmentId进行分组并在组内进行排序。
from
Employee as a1
left join
Department as a2
on
a1.departmentId = a2.id
)as a3
where
ranks<=3