题解 |max嵌套，或者自查询

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 salary = (
    select max(salary)
    from salaries
    where salary < (
        select max(salary)
        from salaries
    )
)

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 -- 当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2
        having
            count(distinct s2.salary) = 2 -- (去重之后的数量就是对应的名次)
    )