关于排名的六种解法

#1、where标量子查询+distinct去重+limit分页查询

select * from employees
where hire_date = (
            select distinct hire_date from employees 
            order by hire_date desc
            limit 2,1
           );

#2、where标量子查询+group by去重合并+limit分页查询

select * from employees
where hire_date = (
             select hire_date 
             from employees 
             group by hire_date
             order by hire_date desc
             limit 2,1
           );

#3、开窗函数dense_rank()+from分页查询

1. 窗口函数：<窗口函数> OVER ( [PARTITION BY <列清单> ] ORDER BY <排序用列清单> ）      其中[ ]中的内容可以忽略，忽略后默认针对整个表
2. 窗口函数之序列函数

序列函数将select查询到的分组数据进行排序

常见的3种序列函数：

row_number()：唯一的连续排名       

例如，有3条排在第1位时，排序为：1，2，3，4······

rank()：不唯一的不连续排名             例如，有3条排在第1位时，排序为：1，1，1，4······

dense_rank()：不唯一的连续排名     

例如，有3条排在第1位时，排序为：1，1，1，2······

select 
t.emp_no
,t.birth_date
,t.first_name
,t.last_name
,t.gender
,t.hire_date
from (
     select 
     *
     ,dense_rank() over(order by hire_date desc) as `rank`
     from employees 
   ) t
where t.`rank` = 3;

#4、开窗函数dense_rank()+join

select a.*
from employees a
left join (
                select
                distinct emp_no
                ,dense_rank() over (order by hire_date desc) as `rank`
                from employees
            ) b
on a.emp_no=b.emp_no
where b.`rank`=3

#5、自连接比较+去重计数+where标量子查询

select *
from employees e3
where e3.hire_date = (
               select e1.hire_date                      -- 当e1<=e2链接并以e1.hire_date分组时一个e1会对应多个e2
               from employees e1
               join employees e2 on e1.hire_date <= e2.hire_date
               group by e1.hire_date
               having count(distinct e2.hire_date) = 3           -- 去重计数的结果即为hire_date的名次
            );

#6、自连接比较+去重计数+join

select e3.*
from employees e3
join
(
    select e1.hire_date                              -- 当e1<=e2链接并以e1.hire_date分组时一个e1会对应多个e2
    from employees e1
    join employees e2 on e1.hire_date <= e2.hire_date             
    group by e1.hire_date
    having count(distinct e2.hire_date) = 3                   -- 去重计数的结果即为hire_date的名次
) a
on e3.hire_date = a.hire_date;