MySQL新增排名字段

淩亂°似流年 2023-09-28 10:49 39阅读 0赞

前言：MySQL在5.X的版本中，没有排名相关函数，如row\_number()、rank()、dense\_rank()等，所以需要用变量的方式去实现这个需求，下面本文简单对三种情况进行情况说明。

如下图为简单的测试数据：

![format_png][] 测试数据

### 一、同分情况下，排名连续 ###

SELECT
    	t1.*,
    	@rownum := @rownum + 1 AS rank 
    FROM
    	( SELECT * FROM `score` ORDER BY score DESC ) t1,
    	( SELECT @rownum := 0 ) t2

![format_png 1][] 如张三和李四分数相同，排名连续

二、同分情况下，同分排名相同且排名连续

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

![format_png 2][] 如张三李四田七，张三李四分数相同排名相同且田七的排名连续

三、同分情况下，同分排名相同但排名不连续

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

![format_png 3][] 如张三李四田七，张三李四分数相同排名相同且李四占用了排名，田七排名不连续

[format_png]: https://img-blog.csdnimg.cn/img_convert/244c34207f4218c6a152e41398c95f4d.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/cd234aea775726052904178b93853bb5.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/236a5e65621239853ed919eec5e4e8c3.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/1fb2a433cb2fae6f166ccc96187d7393.webp?x-oss-process=image/format,png