SQL18获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

https://www.nowcoder.com/practice/c1472daba75d4635b7f8540b837cc719?tpId=82&tags=&title=&difficulty=0&judgeStatus=0&rp=1

题解来自:https://blog.nowcoder.net/n/f35b41269fd84707a748724827510e23?f

方法一好理解

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 max(salary) -- 第二步: 查出除了原表最高工资以外的最高工资(第二高工资)
from salaries
where salary <
(
select max(salary) -- 第一步: 查出原表最高工资
from salaries
where to_date = '9999-01-01'
)
and to_date = '9999-01-01'
)
and s.to_date = '9999-01-01'


方法二:

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 -- (去重之后的数量就是对应的名次)
and s1.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
)
and s.to_date = '9999-01-01'