题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#
获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
http://www.nowcoder.com/practice/c1472daba75d4635b7f8540b837cc719
-- 方法一
select
s.emp_no,
s.salary,
e.last_name,
e.first_name
from
salaries s,
employees e
where
s.emp_no = e.emp_no
and 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,
employees e
where
s.emp_no = e.emp_no
and 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'
-- 方法二
selects.emp_no,
s.salary,
e.last_name,
e.first_name
from
salaries s,
employees e
where
s.emp_no = e.emp_no
and s.salary = (
select
s1.salary
from
salaries s1,
salaries s2
where
s1.salary <= s2.salary
and s1.to_date = '9999-01-01' -- 注意 ：这里and 要和 where 放一起 不然 通过不了
and s2.to_date = '9999-01-01'
group by
s1.salary -- 当s1<=s2,并以s1.salary分组时一个s1会对应多个s2
having
count(distinct s2.salary) = 2 -- 去重后，与s1对应的s2的个数就是s1的名次
)
and s.to_date = '9999-01-01'
