sql面试题目筛选员工表每个部门工资前三名的员工信息

上个星期面试了一份sql开发的工作&＃xff0c;最后一道题目非常有意思&＃xff0c;拿出来跟大家分享下。
有如下两个tables:
department:
department_id,department_name
1 IT
2 Sale

employee
employee_id,name,salary,department_id
1 Joy 1000 1
2 Uncle 1500 1
3 Micky 2500 1
4 John 1500 1
5 Bella 4500 2
6 Ben 5000 2
7 Alex 1000 2
8 Cathy 1400 2

要求筛选出每一个部门工资前三名的员工信息&＃xff08;含部门名&＃xff09;
首先我想到了用聚合函数max&＃43;union 的方法。
select * from employee where salary &＃61; (select max(salary)from employee)

但是酱紫只能筛选所有部门薪酬最高的员工&＃xff0c;没办法做到按照不同部门实现排序。
又总不能在where语句中指定department_id &＃61;1 or 2&＃xff0c;因为实际情况是你不知道有多少个部门。

正解&＃xff1a;
首先要用到oracle的窗口函数 row_number()&＃43;over().
select name,salary,department_id,
row_number() over(partition by department_id order by salary desc)as rank
from employee

然后再做二次/三次筛选&＃xff1a;

select b.name,b.salary,b.department_id,c.department_name,b.rank from
(
select a.name,a.salary,a.department_id,a.rank from
(
select name,salary,department_id,
row_number() over(partition by department_id order by salary desc)as rank
from employee
)a
where a.rank<&＃61;3
)b
inner join
department c
on b.department_id&＃61;c.department_id