获取当前薪水第二多的员工的emp_no对应的薪水salary
获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
https://www.nowcoder.com/practice/c1472daba75d4635b7f8540b837cc719
select
s.emp_no,
s.salary,
e.last_name,
e.first_name
from
salaries s
join employees e on s.emp_no = e.emp_no
where
s.salary = (
select
s1.salary
from
salaries s1
join salaries s2 on s1.salary <= s2.salary
group by
s1.salary
having
count(s1.salary) = 2
);
/*
-- 解释
-- 为什么 having count(s1.salary) =2 -- (分组计数之后的数量就是对应的名次)
-- 执行下列sql拿到数据如下所示：
select s1.salary,s2.salary
from salaries s1 join salaries s2
on s1.salary <= s2.salary
group by s1.salary,s2.salary
order by s1.salary desc;
s1 ,s2
88958,88958
74057,74057
74057,88958
72527,72527
72527,74057
72527,88958
43311,43311
43311,72527
43311,74057
43311,88958
通过对s1 取having count(s1) 可得具体第几大数
*/
腾讯一面2164人在聊
