极快实现榜单排名的需求

create table praise_record(
    id bigint primary key auto_increment,
    name varchar(10),
    praise_num int
) ENGINE=InnoDB;
复制代码

INSERT INTO praise_record (name, praise_num) VALUES ('John', 5);
INSERT INTO praise_record (name, praise_num) VALUES ('Jane', 3);
INSERT INTO praise_record (name, praise_num) VALUES ('Bob', 10);
INSERT INTO praise_record (name, praise_num) VALUES ('Alice', 3);
INSERT INTO praise_record (name, praise_num) VALUES ('David', 7);
INSERT INTO praise_record (name, praise_num) VALUES ('oct', 7);
复制代码

## 注意这里返回的rank字段要用反引号包起来
select name, praise_num, rank() over (order by praise_num desc) as `rank` from praise_record;
 ------- ------------ ------ 
| name  | praise_num | rank |
 ------- ------------ ------ 
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    5 |
 ------- ------------ ------ 

复制代码

select name, praise_num, dense_rank() over (order by praise_num desc) as `rank` from praise_record;
 
  ------- ------------ ------ 
| name  | praise_num | rank |
 ------- ------------ ------ 
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    3 |
| Jane  |          3 |    4 |
| Alice |          3 |    4 |
 ------- ------------ ------ 
复制代码

select name, praise_num, row_number() over (order by praise_num desc) as `rank` from praise_record;
  ------- ------------ ------ 
| name  | praise_num | rank |
 ------- ------------ ------ 
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    3 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    6 |
 ------- ------------ ------ 
 
复制代码

select p1.name, p1.praise_num, count(p2.praise_num)   1 as `rank` from praise_record p1
left join praise_record p2 on p1.praise_num < p2.praise_num
group by p1.name, p1.praise_num
order by `rank`;
 ------- ------------ ------ 
| name  | praise_num | rank |
 ------- ------------ ------ 
| Bob   |         10 |    1 |
| David |          7 |    2 |
| oct   |          7 |    2 |
| John  |          5 |    4 |
| Jane  |          3 |    5 |
| Alice |          3 |    5 |
 ------- ------------ ------ 
复制代码

select p1.name, p1.praise_num, count(distinct p2.praise_num)   1 as `dense_rank` from praise_record p1
left join praise_record p2 on p1.praise_num < p2.praise_num
group by p1.name, p1.praise_num
order by `dense_rank`;
 ------- ------------ ------------ 
| name  | praise_num | dense_rank |
 ------- ------------ ------------ 
| Bob   |         10 |          1 |
| oct   |          7 |          2 |
| David |          7 |          2 |
| John  |          5 |          3 |
| Jane  |          3 |          4 |
| Alice |          3 |          4 |
 ------- ------------ ------------ 
复制代码

##使用自定义变量得先初始化
set @rowNum = 0;
select name, praise_num, @rowNum := @rowNum  1 as `row_number`  from praise_record order by praise_num desc ;
 ------- ------------ ------------ 
| name  | praise_num | row_number |
 ------- ------------ ------------ 
| Bob   |         10 |          1 |
| David |          7 |          2 |
| oct   |          7 |          3 |
| John  |          5 |          4 |
| Jane  |          3 |          5 |
| Alice |          3 |          6 |
 ------- ------------ ------------ 
复制代码