题解 | #获取当前薪水第二多的员工的emp_no#

select a.emp_no,salary,last_name,first_name
from salaries a 
left join employees b 
on a.emp_no  = b.emp_no 
where 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 to_date = '9999-01-01'

确实有点麻烦，先找出最高的工资，然后作为where的条件，在筛选出第二高的工资，并也作为where的条件，最后用这个条件找出工资和第二高一样的人的详细信息

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 a.salary
from salaries  a 
join salaries b 
on a.salary < b.salary
where  a.to_date = '9999-01-01'and b.to_date = '9999-01-01'
group by a.salary
having count(distinct b.salary) =1 

)

让薪水表自己形成大小关系，然后取第一行中小于的那一方值