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

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

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Chirstian	Koblick	M	1986-12-01

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

emp_no	salary	from_date	to_date
10001	88958	2002-06-26	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01
10004	74057	2001-11-27	9999-01-01

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

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

emp_no	salary	last_name	first_name
10004	74057	Koblick	Chirstian

示例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

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3304KB, 提交时间: 2020-12-13

select e.emp_no, MAX(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 MAX(distinct s1.salary) from salaries s1 where s1.to_date='9999-01-01')

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3304KB, 提交时间: 2020-09-08

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
and s.to_date = '9999-01-01'
and s.salary = (select max(salary) from salaries where salary < (select max(salary) 
                              from salaries 
                              where to_date='9999-01-01'
                             ) and to_date='9999-01-01')

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3360KB, 提交时间: 2021-09-09

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

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3360KB, 提交时间: 2021-07-25

select e.emp_no, Max(s.salary) salary, e.last_name, e.first_name

from employees e,salaries s

where e.emp_no=s.emp_no

and s.salary!= (select Max(salary) from salaries where to_date='9999-01-01')

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3364KB, 提交时间: 2022-01-23

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 s.salary=(
select max(salary) from salaries where salary<>(select max(salary) from salaries))

上一题

下一题

详情