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

1、使用子查询 + LIMIT + OFFSET

select *

from employees

where hire_date = (

select distinct hire_date

from employees

order by hire_date desc

limit 1 offset 2

);

2、使用窗口函数 (RANK())

with ranked_employees as

(select

emp_no,

birth_date,

first_name,

last_name,

gender,

hire_date,

dense_rank() over (order by hire_date desc) ranking

from employees)

select

emp_no,

birth_date,

first_name,

last_name,

gender,

hire_date

from ranked_employees

where ranking=3

order by emp_no;