【SQL216】题解 | 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no, salary from (select emp_no, salary, dense_rank() over (order by salary desc) as rk from salaries) s where rk = 2 order by emp_no;

SQL216

这道题获取薪水第二多的员工的emp_no以及其对应的薪水salary，若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序

两个关键点：

1.使用可重复排序的窗口函数dense_rank() over (order by xxx asc/desc) as rk from xxx进行排序，获取薪水第二多的员工的emp_no以及其对应的薪水salary

 (select emp_no, salary, dense_rank() over (order by salary desc) as rk from salaries) s where rk = 2
 # 后面在最外层的时候使用了子查询或者说外层查询

2.按emp_no升序排序

order by emp_no

完整代码如下：

select emp_no, salary from (select emp_no, salary, dense_rank() over (order by salary desc) as rk from salaries) s where rk = 2 order by emp_no;

完结