SQL 实战 18题 题解

题目：
查找当前薪水(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有多个
)