MySQL查询排名

sql语句查询排名

思路：有点类似循环里面的自增一样，设置一个变量并赋予初始值，循环一次自增加1，从而实现排序；

 

首先准备测试数据（用户id，姓名，性别，分数）：

需求：根据分数实现分数由高到低的一个排名

 

第一种情况：

将已经排序好的数据从第一条依次取出来，取一条就自增加一，实现从1到最后的一个排名（意思就是不管分数相同与否，排名依次排序，即：1,2,3,4,5,6,7,……）

 

Sql如下：

SELECT
a.name,
a.score,
@rownum := @rownum + 1 AS rownum
FROM
(SELECT
name,
score
FROM tab1
ORDER BY score DESC) AS a,
(SELECT @rownum := 0) r

执行的结果如下图：

可以看到，现在按照分数从1到6都排好序了，但是有些分数相同的用户排名却不一样，这样就是接下来说的第二种情况。

 

第二种情况：

    当出现相同的数据时，排名保持不变，此时则需要再设置一个变量，用来记录上一条数据的值，跟当前数据的值进行对比，如果相同，则排名不变，不相同则排名自增加1（意思就是只要数据有相同的排名就一样，排名依次排序，即1,2,2,3,3,4,5……）

 

Sql如下：

SELECT
	a.name,
	a.score,
	CASE
	WHEN @rowtotal = a.score THEN
			@rownum
	WHEN @rowtotal := a.score THEN
			@rownum :=@rownum + 1
	WHEN @rowtotal = 0 THEN
			@rownum :=@rownum + 1
	END AS rownum
FROM
	(SELECT
		name,
		score
	FROM tab1
	ORDER BY score DESC) AS a,
(SELECT @rownum := 0 , @rowtotal := NULL) r

这时候就新增加了一个变量，用于记录上一条数据的分数了，只要当前数据分数跟上一条数据的分数比较，相同分数的排名就不变，不相同分数的排名就加一，并且更新变量的分数值为该条数据的分数，依次比较

 

执行的结果如下图：

如果你需要分数相同的排名也相同，但是后面的排名不能受到分数相同排名相同而不占位的影响，也就是哪怕你排名相同，你也占了这个位置（比如：1,2,2,4,5,5,7……这种形式的，虽然排名有相同，但是你占位了，后续的排名根据占位来排，也就是我要说的第三种情况）

 

第三种情况：

    当出现相同的数据时，排名保持不变，但是保持不变的排名依旧会占用一个位置，也就是类似于（1,2,2,2,5即虽然三个人并列第二名，但是第三和第四名的位置也会被占用）这种排名就是属于中间的三个排名是一样的，但是第五个排名按照上面一种情况是（1,2,2,2,3），现在则是排名相同也会占据排名的位置

 

为了让大家更加直观的对比第一种情况与第三种情况的差别，在sql中我又增加了一个变量，来记录第一种情况的排名（num_tmp为第一种情况的排名，rownum为第三种情况的排名）

 

Sql如下：

SELECT
		a.name,
		a.score,
		@rownum := @rownum + 1 AS num_tmp,
		@incrnum := CASE
		WHEN @rowtotal = a.score THEN @incrnum
		WHEN @rowtotal := a.score THEN @rownum
		END AS rownum
FROM
		(SELECT
			name,
			score
		FROM tab1
		ORDER BY score DESC) AS a,
(SELECT @rownum := 0 , @rowtotal := NULL ,@incrnum := 0) r

执行的结果如下图：

本人初学，如有发现内容错误，请及时评论，我好更正修改，谢谢大家！