MySQL使用变量实现各种排序

核心代码

--下面我演示下mysql中的排序列的实现
--测试数据
create table tb
(
score int
);
insert tb select 
5 union all select 
4 union all select 
4 union all select 
4 union all select 
3 union all select 
2 union all select
1;
--1.row_number式的排序
set @row_number =0;
select @row_number := @row_number+1 as row_number,score 
from tb 
order by score desc ;
+------------+-------+
| row_number | score |
+------------+-------+
|     1 |   5 |
|     2 |   4 |
|     3 |   4 |
|     4 |   4 |
|     5 |   3 |
|     6 |   2 |
|     7 |   1 |
+------------+-------+
--2.dense_rank式的排序
set @dense_rank = 0,@prev_score = null;
select @dense_rank :=if(@prev_score=score,@dense_rank,@dense_rank+1) as decnse_rank,
  @prev_score := score as score 
from tb 
order by score desc ; 
+-------------+-------+
| decnse_rank | score |
+-------------+-------+
|      1 |   5 |
|      2 |   4 |
|      2 |   4 |
|      2 |   4 |
|      3 |   3 |
|      4 |   2 |
|      5 |   1 |
+-------------+-------+
--3.rank式的排序
set @row=0,@rank=0,@prev_score=null;
select @row:=@row+1 as row,
    @rank:=if(@prev_score=score,@rank,@row) as rank,
    @prev_score:=score as score
from tb 
order by score desc;
+------+------+-------+
| row | rank | score |
+------+------+-------+
|  1 |  1 |   5 |
|  2 |  2 |   4 |
|  3 |  2 |   4 |
|  4 |  2 |   4 |
|  5 |  5 |   3 |
|  6 |  6 |   2 |
|  7 |  7 |   1 |
+------+------+-------+