题解 | 三种方法#查找入职员工时间排名倒数第三的员工所有信息#

-- 使用窗口函数
select
emp_no,
birth_date,
first_name,
last_name,
gender,
hire_date
from
(
select
*,
dense_rank() over (
order by
hire_Date desc
) rk
from
employees
) a
where
rk = 3

-- 使用自连接
select
emp_no,
birth_date,
first_name,
last_name,
gender,
hire_date
from
(
select
a.*,
count(b.hire_date) as num
from
employees a
left join employees b on a.hire_date < b.hire_date
group by
a.emp_no,
a.birth_date,
a.first_name,
a.last_name,
a.gender,
a.hire_date
) c
where
num = 2

-- 使用子查询，利用limit分页
SELECT *
FROM employees
WHERE hire_date = (
SELECT DISTINCT hire_date
FROM employees
ORDER BY hire_date DESC -- 倒序
LIMIT 1 OFFSET 2 -- 去掉排名倒数第一第二的时间，取倒数第三
);