条件
员工表 -- employees
薪水表 -- salaries
要求
1、薪水第二名的员工
2、输出格式为emp_no、salary、last_name、first_name
3、不能使用order by
分析
根据要求1、3分析,得:
不能用order by的情况下可以通过两种方式去获取结果
1、最大值法:找出最大值的限制下再找出最大值,即使用两次max()
2、序号法:通过salaries表的自我对比,得到s1(s1<s2,即当s2存在时s1表里的值为次大值),最后通过限制s2值的数量n,找到第n+1的值(没有最小值)
根据要求2分析,得:
- emp_no -- 由employees表获得
- salary -- 由salaries获得
- last_name -- 由employees获得
- first_name -- 由employees获得
- 由于涉及多个表,需要用到join来匹配对应的值
做题
最大值法:
select em.emp_no, sa.salary, em.last_name, em.first_name
from employees as em join salaries as sa
on em.emp_no = sa.emp_no
and sa.salary =
( select max(salary) from salaries
where salary != (select max(salary) from salaries)
) ;
序号法:
select em.emp_no, sa.salary, em.last_name, em.first_name
from employees as em join salaries as sa
on em.emp_no = sa.emp_no
and sa.salary =
( select s1.salary from salaries as s1
join salaries as s2
on s1.salary < s2.salary //s1的值比s2的值小,当s2有2个值时,s1的值即为次大值
group by s1.salary
having count(distinct s2.salary ) = 1 //注意有多个最大值
) ;