不用order by 选出第二个人

SQL212 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

• 题目
• 题解(273)
• 讨论(774)
• 排行
• 面经

  new

较难  通过率：28.24%  时间限制：1秒  空间限制：32M

描述

有一个员工表employees简况如下:

有一个薪水表salaries简况如下:

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:

（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)

示例1

输入：

drop table if exists  `employees` ; 
drop table if exists  `salaries` ; 
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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

复制输出：

10004|74057|Koblick|Chirstian

select employees.emp_no, s1.salary, last_name, first_name
from employees
left join salaries s1
on employees.emp_no = s1.emp_no
where (
    select sum(s2.salary>s1.salary)
    from salaries s2
) = 1  -- sum值为1的说明取得第二个人 sum值为 n 的 说明取得是排名为 n+1的人

用的max

with t1 as (
select
max(salary) salary_2_max
from
salaries
where salary != (
select
max(salary) 
from
salaries)
    )
    select
    employees.emp_no,salaries.salary,employees.last_name,employees.first_name
    from 
    t1 join  salaries join employees
    on t1.salary_2_max = salaries.salary and salaries.emp_no = employees.emp_no