SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

描述

有一个员工表employees简况如下:

有一个薪水表salaries简况如下:

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:

（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)

/*
题目：请你查找薪水排名第二多的员工编号
emp_no、薪水salary、last_name以及first_name，不能使用order by完成
*/
-- 方法一：可以通过MAX配合<运算得到

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 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'


-- 方法二：可以通过自连接和<=号配合COUNT函数找出排名高的薪水
-- 补充：第二种方法可以推广到求薪水排名第N高的情况
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 s1.salary
 FROM salaries AS s1
 INNER JOIN salaries AS s2
 ON s1.to_date='9999-01-01'
 AND s2.to_date='9999-01-01'
 WHERE s1.salary <= s2.salary
 GROUP BY s1.salary
 HAVING COUNT(DISTINCT s2.salary)=2
);