注意点:可能存在并列第一、并列第二的情况
思路一:在子查询中去重
方法一:group by去重
select emp_no, salary from salaries where to_date = '9999-01-01' and salary = ( select salary from salaries where to_date = '9999-01-01' group by salary order by salary desc limit 1,1 );
方法二:distinct去重
select emp_no, salary from salaries where to_date = '9999-01-01' and salary = ( select distinct salary from salaries where to_date = '9999-01-01' order by salary desc limit 1,1 );
思路二:开窗函数
select t.emp_no, t.salary from( select emp_no, salary, dense_rank() over (order by salary desc) as rk from salaries where to_date ='9999-01-01' )as t where rk = 2
dense_rank() /rank() /row_number()的选择:
如果有两个并列第一,两个并列第二:
rank()是跳跃排序,结果是1133;
dense_rank()是连续排序,结果是1122;
row_number()连续且唯一,结果是1234。