题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

• 本题解析
  这是一题求第几名的题目,平时用order by和limit比较好解决,不用的话求起来比较麻烦,也不太好理解,不过多操作几次就明白了
• 参考答案

select 
e.emp_no,
s.salary,
e.last_name,
e.first_name
from employees e join salaries s 
on e.emp_no = s.emp_no
and s.to_date='9999-01-01'
where s.salary = (
    select s1.salary from salaries s1 join salaries s2
    on s1.salary <= s2.salary
    and s1.to_date='9999-01-01'
    and s2.to_date='9999-01-01'
    group by s1.salary
    having count(distinct s2.salary)=2)