方法)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';