前言:MySQL在5.X的版本中,没有排名相关函数,如row_number()、rank()、dense_rank()等,所以需要用变量的方式去实现这个需求,下面本文简单对三种情况进行情况说明。
如下图为简单的测试数据:
![](https://i-blog.csdnimg.cn/blog_migrate/e3fa3a602872a8e59408a7fe6a274342.png)
一、同分情况下,排名连续
SELECT
t1.*,
@rownum := @rownum + 1 AS rank
FROM
( SELECT * FROM `score` ORDER BY score DESC ) t1,
( SELECT @rownum := 0 ) t2
![](https://i-blog.csdnimg.cn/blog_migrate/a4643bd998edaf1d7e2d0070a9d06dba.png)
二、同分情况下,同分排名相同且排名连续
SELECT
t1.*,
(
CASE
WHEN @rowtotal = score THEN
@rownum
WHEN @rowtotal := score THEN
@rownum := @rownum + 1
WHEN @rowtotal = 0 THEN
@rownum := @rownum + 1
END
) AS rank
FROM
( SELECT * FROM `score` ORDER BY score DESC ) AS t1,
( SELECT @rownum := 0, @rowtotal := NULL ) t2
![](https://i-blog.csdnimg.cn/blog_migrate/915be431a3d1f4034f2d34f7e31f776e.png)
三、同分情况下,同分排名相同但排名不连续
SELECT
t1.*,
@rownum := @rownum + 1 AS rank_tmp,
@incrnum := ( CASE WHEN @rowtotal = t1.score THEN @incrnum WHEN @rowtotal := t1.score THEN @rownum END ) rank
FROM
( SELECT * FROM `score` ORDER BY score DESC ) t1,
( SELECT @rownum := 0, @rowtotal := NULL, @incrnum := 0 ) t2
![](https://i-blog.csdnimg.cn/blog_migrate/564ef4d0dc70e68dd819851923f9b798.png)