MySQL新增排名字段

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

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

测试数据

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

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

如张三和李四分数相同，排名连续

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

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

 

如张三李四田七，张三李四分数相同排名相同且田七的排名连续

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

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

 

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