牛客SQL练习第18题

题目描述

查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解答：

先用和上一题一样的思路：

 select e.emp_no,s.salary,e.last_name,e.first_name
 from employees e
 inner join salaries s
 on e.emp_no=s.emp_no
 where salary=(
     select distinct salary 
     from salaries 
     where to_date='9999-01-01' 
     order by salary desc 
     limit 1,1 
     )

题目要求不能用order by，想要得到排第几的数据，可以使用自连接：

select e.emp_no,s.salary,e.last_name,e.first_name
from
employees e
join 
salaries s on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and s.salary = 
(
     select s1.salary
     from 
     salaries s1 join salaries s2 on s1.salary<=s2.salary 
     and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
     group by s1.salary
     having count(distinct s2.salary)=2
 )

假如：
s1：100，100，99，98
s2：100，100，99，98
那么：
s1.100关联s2.100，s2.100
s1.99关联s2.100，s2.100，s2.99
s1.98关联s2.100，s2.100，s2.99，s2.98

对s1的工资进行分组 ，累计s1关联的s2的不重复的工资个数，此即为s1该工资的排名。题目要求是第2名，如果要求第5名， 改成count(distinct s2.salary)=5即可。