题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#
获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
http://www.nowcoder.com/practice/c1472daba75d4635b7f8540b837cc719
//找到前2名,然后取第二个.
SELECT
t2.emp_no ,t2.salary ,
(SELECT t3.last_name from employees t3 where t2.emp_no=t3.emp_no ) last_name,
(SELECT t3.first_name from employees t3 where t2.emp_no=t3.emp_no ) first_name
from salaries t2
where 2 > (
select count(1)
from salaries t1
where t1.salary > t2.salary )
and t2.salary < (select max(t1.salary) from salaries t1 )
先找到前几名,注意,这里是取笛卡尔积,然后根据条件进行比较:
select t2.salary
from salaries t2
where 2 > (
select count(1)
from salaries t1
where t1.salary > t2.salary )
查看5道真题和解析