题解 | SQL212|
获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
https://www.nowcoder.com/practice/c1472daba75d4635b7f8540b837cc719
select
e.emp_no
,s.salary
,e.last_name
,e.first_name
from employees as e
join salaries as s on (e.emp_no = s.emp_no)
where s.salary = ---用第二大的值作为筛选条件
(
select --- 找出第二大值
distinct max(s.salary)over() as max_2_salary --- 去重
from employees as e
join salaries as s on (e.emp_no = s.emp_no)
where s.salary !=
(
select --- 找出最大值
distinct max(s.salary)over() as max_1_salary --- 去重
from employees as e
join salaries as s on (e.emp_no = s.emp_no)
)
)
