题解 | #不使用排序获取薪水第二多的员工信息#

select
    s.emp_no, s.salary, e.last_name, e.first_name
from
    salaries s join employees e
on
    s.emp_no = e.emp_no
where s.salary =
(
    select
        s1.salary
    from
        salaries s1 join salaries s2
    on
        s1.salary <= s2.salary
    group by
        s1.salary                     
    having
        count(distinct s2.salary) = 2
);

使用自联结：s1.salaries <= s2.salaries时，排名第n的薪水会有n个s2.salaries大于s1.salaries，再根据s1.salaries分组后统计个数即可得到排名。