题目:
查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name及first_name,不使用order by
要点分析:
1.如果只是查找“当前薪水排名第二多的员工”利用排序和“limit offset”语句可以轻易实现。但这里要求不能够使用order by。
2.所以有两种思路:
1)将最多的salary对应的员工从表中剔除,然后再在剩余的员工之中寻找salary最多的员工。
2)利用salaries表的子链接,人为实现两个子表s1、s2之间的连接,按照s1.salary与s2.salary进行配对。筛选时,要求s1.salary<=s2.salary,同时以s1.salary为基准进行分组,那么这意味着一条s1.salary对应着至少一条s2.salary,而第二高的s1.salary对应着两条s2.salary,那么语句中对s2.salary进行count即可,为2的那一条对应的s1.salary就是表中第二高的薪水。
注:
注意当前这一限制条件
语句实现:
方法一
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.to_date ='9999-01-01' and salary = ( select max(salary) from salaries where s.to_date ='9999-01-01' and salary < ( select max(salary) from salaries where s.to_date ='9999-01-01' ) --构造剔除掉最大salary的salaries表 )
方法二
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.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 --这里要注意的是distinct的使用,因为很用可能同样数目的salary有多个 )