查找薪水第二多的员工基本信息

    题目描述
 查找当前薪水(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`));

 输入描述:

关于查询第二多，方式非常多，而题目要求不准使用 order by 来排序. 分享我写3种方式查询第二

sql code:

方案一:
 select s.emp_no,s.salary ,e.last_name from  salaries s, employees e where s.to_date = '9999-01-01' and s.emp_no = e.emp_no 
and s.salary = (select min(salary) from (select  s.salary from  salaries s  where s.to_date =              '9999-01-01' ORDER BY s.salary desc LIMIT 2
) t )
order by s.salary desc  LIMIT 2 

 方案二:
  -- 先查询出第二多的金额
       select max(s.salary)  from  salaries s where s.salary not in (select max(s.salary) from         salaries s) and s.to_date ='9999-01-01'

   -- 完整的sql 
  select s.emp_no,s.salary, e.last_name ,e.first_name from salaries s ,employees e   where s.salary = 
 ( select max(s.salary)  from  salaries s where s.salary not in (select max(s.salary) from  salaries s) and s.to_date ='9999-              01-01')
and e.emp_no =s.emp_no and s.to_date ='9999-01-01'


  方案三:

   select s.emp_no,s.salary,e.last_name,e.first_name
   from salaries s  ,employees e 
   where to_date='9999-01-01' and e.emp_no = s.emp_no 
   order by salary desc
   limit 1,1

方案二: 是没有用到 order by 来排序的