题解 | #SQL 212获取当前薪水第二多的员工信息

select e.emp_no,s.salary,e.last_name,e.first_name
from salaries s
left join 
employees e
on s.emp_no=e.emp_no
where salary = (
    select max(salary)
    from salaries
    where salary <>(select max(salary) from salaries)
)

利用嵌套max函数求解

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

利用自连接配合聚合函数count得到排名然后筛选