MySql 实现数据排名各版本总结

 

一、MySql 8.0 以下 实现排名

-- 方法一
SELECT name, score, @rank := @rank + 1 as pm
FROM scores A,
     (SELECT @rank := 0) B
ORDER BY score DESC;

-- 方法二
select C.*
from (
         SELECT A.*, @rank := @rank + 1 as pm
         FROM (
                  SELECT name, score
                  FROM scores
                  ORDER BY score DESC
              ) A,
              (SELECT @rank := 0) B
     ) as C;

-- 方法三
select B.*,
       @curBank := IF(@prevRank = B.score, @curBank, @incRank) as 'pm',
       @incRank := @incRank + 1                                as 'inc',
       @prevRank := B.score                                    as 'sc'
from (
         select name, score
         from scores A,
              (select @curBank := 0, @prevRank := null, @incRank := 1) r
         group by name
     ) as B
order by B.score desc;

-- 方法四
select B.*,
       @curBank := IF(@prevRank = B.score, @curBank, @incRank) as 'pm',
       @incRank := @curBank + 1                                as 'inc',
       @prevRank := B.score                                    as 'sc'
from (
         select name, score
         from scores A,
              (select @curBank := 0, @prevRank := null, @incRank := 1) r
         group by name
     ) as B
order by B.score desc;
　　　　　　　　

 

结果一（方法一、方法二）：

   

 

 

结果二（方法三）：

   

 

结果三（方法四）：

    

 

 

二、MySql 8.0 实现排名

 

函数	作用
RANK	并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。
DENSE_RANK	并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。
ROW_NUMBER	连续排名，即使相同的值，依旧按照连续数字进行排名。

　　

-- 方法一
select name,score, rank() over(ORDER BY score desc) as 'pm' from scores;

-- 方法二
select name,score, dense_rank() over(ORDER BY score desc) as 'pm' from scores;

-- 方法三
select name,score, row_number() over(ORDER BY score desc) as 'pm' from scores;

 

结果一：

   

 

  

结果二：

   

 

 

结果三：