注意点:可能存在并列第一、并列第二的情况

思路一:在子查询中去重

方法一: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。