SQL 18) -dense_rank 查找当前薪水排名第二多的员工

方法）dense_rank（）over（）

• 不使用order by
• 当前薪水(to_date='9999-01-01')

SELECT e.emp_no, a.salary, e.last_name, e.first_name
FROM employees AS e JOIN ( 
    SELECT emp_no, salary, dense_rank()over(ORDER BY salary DESC)rank_num
    FROM salaries
    WHERE to_date = '9999-01-01')a
ON a.emp_no = e.emp_no
WHERE a.rank_num = 2;

21/3/24
补充方法 2）现在用order by 会报错，更换为以下的方式

• 排除掉薪水最多的员工，那么薪水第二多的就是薪水最多的了

select e.emp_no, s.salary, e.last_name, e.first_name 
from employees as e, salaries as s
where e.emp_no = s.emp_no
and s.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'))
and s.to_date = '9999-01-01';