首页 > 试题广场 >

查找入职员工时间排名倒数第三的员工所有信息

[编程题]查找入职员工时间排名倒数第三的员工所有信息
  • 热度指数:799301 时间限制:C/C++ 1秒,其他语言2秒 空间限制:C/C++ 32M,其他语言64M
  • 算法知识视频讲解
有一个员工employees表简况如下:
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
Christian
Koblick
M 1986-12-01

请你查找employees里入职员工时间排名倒数第三的员工所有信息,以上例子输出如下:
emp_no
birth_date
first_name
last_name
gender
hire_date
10001
1953-09-02
Georgi
Facello
M 1986-06-26
注意:可能会存在同一个日期入职的员工,所以入职员工时间排名倒数第三的员工可能不止一个。
示例1

输入

drop table if exists  `employees` ; 
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`));
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 employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

输出

10005|1955-01-21|Kyoichi|Maliniak|M|1989-09-12
select * from employees 
where hire_date = (
    select distinct hire_date from employees order by hire_date desc limit 2,1
)
LIMIT m,n : 表示从第m+1条开始,取n条数据;
LIMIT n : 表示从第0条开始,取n条数据,是limit(0,n)的缩写。

(1)首先需要加distinct去重。
假设 5-23(入职最晚日期)入职的有a,b,c 3人;
        5-22(入职第二晚日期)入职的有d,e 2人;
        5-21(入职倒数第三晚)入职的有f,g,h 3人;
        5-21前入职的若干...
若 不加distinct去重,那么按照日期倒序,limit 2,1从倒数第2行开始,取一条数据)的查询结果为 5-23
加了distinct去重,会按入职日期进行分组,多个相同入职日期会分为一组,这样limit 2,1的结果即为 5-21。
(2)外层的where条件中根据子查询查出的倒数第三晚入职的日期,就能查询出符合条件的员工信息。
编辑于 2019-05-14 13:12:04 回复(72)
select * 
from 
employees
where hire_date=(select distinct hire_date from employees order by hire_date desc limit 2,1);

发表于 2017-09-01 04:28:13 回复(35)
考虑到入职日期可能会有多个重复,最合理的SQL语句应为:
SELECT * FROM employees 
WHERE hire_date = (
SELECT DISTINCT hire_date FROM employees 
ORDER BY hire_date DESC limit 2,1
);
下图为我建立的employees表,注意倒数第三个入职员工有两个日期相同,且倒数第一,倒数第二也有相同的:
运行以上SQL语句的结果为正确的结果
而运行 SELECT * FROM employees ORDER BY hire_date DESC LIMIT 2,1;的结果为下图,是不正确的

运行 SELECT * FROM employees 
WHERE hire_date = (
SELECT hire_date FROM employees 
GROUP BY hire_date ORDER BY hire_date DESC limit 2,1
);的结果也是正确的,如下图:
但是实际测试的结果distinct比group by语句更快,而且内存占用也更小
发表于 2019-03-02 16:47:19 回复(20)
可能有多个记录,所有最佳结果写成如下形式:
SELECT *
FROM
employees 
WHERE hire_date = (SELECT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2,1);
发表于 2017-07-14 21:29:04 回复(17)
为啥都用limit,用窗口函数不是老快了
SELECT a.emp_no, a.birth_date, a.first_name, a.last_name, a.gender, a.hire_date
FROM (SELECT emp_no, birth_date, first_name, last_name, gender, hire_date,
     DENSE_RANK() OVER(ORDER BY hire_date DESC) AS rk
     FROM employees) AS a
WHERE a.rk = 3;

发表于 2021-09-06 14:31:15 回复(12)

思路:按入职时间倒序排列,然后用limit选择第三个即可。


select * from employees order by hire_date desc limit 2,1
发表于 2017-11-08 17:11:38 回复(20)
使用了一个技巧。
只需要找到日期小于其他人数为2即可。(注意倒数第三,其实日期是排第三)

select * from employees e1
where 2=(select count(*) from employees e2 where e1.hire_date < e2.hire_date);
发表于 2017-08-06 00:39:01 回复(12)

一种比较严谨的写法:

SELECT emp_no, birth_date, first_name, last_name, gender, hire_date
FROM employees
WHERE hire_date=(
                SELECT DISTINCT hire_date 
                FROM employees
                ORDER BY hire_date DESC
                LIMIT 2,1)

另外根据阿里巴巴Java开发规范v1.4中,数据库规约,关键词应大写,SELECT后面不要跟着“*”,要把具体的字段写出来。

发表于 2018-11-21 22:21:02 回复(16)
select * from(
select rownum rn ,a.*  from
(SELECT * FROM  employees order by hire_date desc )a )
where rn=3
发表于 2017-08-20 15:45:59 回复(1)
select emp_no,birth_date,first_name,last_name,gender,hire_date from employees where hire_date = 
( select hire_date from employees group by hire_date order by hire_date desc limit 2,1)

group by,order by,limit
至于为什么用group by不用distinct,请看group by与distinct性能比较

发表于 2018-06-14 13:19:15 回复(6)
select * from employees order by hire_date desc limit 2,1
发表于 2017-08-12 09:45:14 回复(0)
select * from employees
where hire_date=(select distinct hire_date from employees order by hire_date DESC limit 2,1)

可能有多个入职员工
排序要去重!!!
发表于 2017-08-12 10:06:39 回复(5)
似乎不支持Oracle的分析函数,累觉不爱
select emp_no,birth_date,first_name,last_name,gender,hire_date from 
(select emp_no,birth_date,first_name,last_name,gender,hire_date,
dense_rank() over (order by hire_date desc) dense_rank
from employees) e
where dense_rank=3;
这样即使有重复的也可以取出来了

发表于 2018-11-06 11:36:01 回复(3)
最最最严谨的解法!
使用相关子查询,可以查询出并列第三(重复值),也不怕前面有重复值

select *
from employees e1
where (select count(distinct e2.hire_date)
      from employees e2
      where e1.hire_date < e2.hire_date) = 2

发表于 2019-10-16 07:01:00 回复(2)
可能有多个
select * from employees where hire_date = (select hire_date from employees order by hire_date desc limit 2,1);

编辑于 2017-07-14 00:53:49 回复(4)
这题关键是去重,不用使用子查询。因为去重是发生在limit和offset关键字之前,所以只需要在第一层查询中添加distinct,搭配排序和limit关键字便可作出答案
select distinct * from employees
order by hire_date desc
limit 1 offset 2


发表于 2020-03-11 21:04:22 回复(4)
LIMIT m,n : 表示从第m+1条开始,取n条数据;
LIMIT n : 表示从第0条开始,取n条数据,是limit(0,n)的缩写。
select * 
from employees 
where 
    hire_date = 
    (
        select distinct hire_date
        from employees
        order by hire_date desc 
        limit 2,1
    )
依题意得:按员工的入职时间进行排序,取倒数第三的时间,只要入职时间等于这个时间的员工都符合题意
还有一种解法
select * 
from employees 
where 
    hire_date = 
    (
        select hire_date
        from employees
        group by hire_date
        order by hire_date desc 
        limit 2,1
    )
去重和分组的效果一样,但是distinct比group by语句更快,内存占用更小
发表于 2021-01-09 11:32:09 回复(1)
这个题目有问题吧!!! 如果大家的成绩都一样,你说谁是倒数第三名呢?
发表于 2018-03-17 09:37:52 回复(5)
SELECT *
FROM employees
WHERE hire_date = (SELECT DISTINCT hire_date 
                   FROM employees 
                   ORDER BY hire_date DESC
                   LIMIT 2,1);

发表于 2017-08-21 20:35:55 回复(0)
新思路:
第一步:利用窗口函数dense_rank()将入职时间hire_date进行排名,从而获取到各个入职时间的排名,并且将其当做一个临时表t
select hire_date,dense_rank() over(order by hire_date desc) date_rank
from employees
第二步:将主表employees和临时表t 通过入职时间hire_date 进行关联查询,同时date_rank满足排名为3。这样就可以获取到相应员工的所有信息了
select distinct e1.* from employees e1
join (
    select hire_date,dense_rank() over(order by hire_date desc) date_rank
    from employees
) t
on e1.hire_date = t.hire_date
and t.date_rank = 3;





发表于 2022-05-06 17:50:04 回复(0)