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

select s.emp_no,s.salary,last_name,first_name
from employees e
join salaries s using(emp_no)
where s.emp_no=(
select emp_no from salaries m
where m.salary= (
select max(salary) from salaries  
where salary < (select max(salary) from salaries) and to_date='9999-01-01') )

第一步，找出当前第二高的工资

select max(salary) from salaries

where salary < (select max(salary) from salaries) and to_date='9999-01-01'

第二步，找出当前第二高的工资的emp_no

select emp_no from salaries m

where m.salary= (

select max(salary) from salaries

where salary < (select max(salary) from salaries) and to_date='9999-01-01')

第三步，查询该emp_no的员工信息

select s.emp_no,s.salary,last_name,first_name

from employees e

join salaries s using(emp_no)

where s.emp_no=(

select emp_no from salaries m

where m.salary= (

select max(salary) from salaries

where salary < (select max(salary) from salaries) and to_date='9999-01-01') )