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