题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

-- max嵌套
select e.emp_no,s.salary,e.last_name,e.first_name
from employees as e inner join salaries as s
    on e.emp_no = s.emp_no
where to_date = '9999-01-01'
and salary = (
    select max(salary) from salaries
    where to_date = '9999-01-01'
    and salary < (
        select max(salary)
        from salaries
        where to_date = '9999-01-01'
    )
);

-- 窗口函数
select b.emp_no,b.salary,e.last_name,e.first_name from
employees as e inner join 
(selcet emp_no,salary,dense_rank() over (partition by to_date order by salary desc) as rk
from salaries
where to_date = '9999-01-01') as b
on e.emp_no = b.emp_no
where rk = 2;

 -- 自连接和count函数计算排名
select e.emp_no,s.salary,e.last_name,e.first_name
from employees as e inner join salaries as s
    on e.emp_no = s.emp_no
where s.salary = 
    (
        select s1.salary
        from salaries as s1 join salaries as s2

        -- <=代表比某个薪水值小的分到一组
        on s1.salary <= s2.salary
        group by s1.salary
        having count(distinct s2.salary) = 2
    );