MySQL实现类似窗口函数（rank）的排名

1、学生表如下，按分数排序，且需要相同成绩的具有相同的排名，输出排名，根据大牛的提供的排名方法，自己做了进一步扩充，实现如下：

备注：需要分数相同的排名也相同，但是后面的排名不能受到分数相同排名相同而不占位的影响，也就是哪怕你排名相同，你也占了这个位置（比如：1,2,2,4,5,5,7....这种形式的，虽然排名有相同，但是你占位了，排名根据占位来排

SELECT 
	tmp.id, tmp.name, tmp.sex, tmp.age, tmp.score, tmp.class, 
	-- 顺序一直在变大
	@j:=@j+1 as j,
	-- 只有在前后二次排序值不同时才会使用顺序号
	@k:=(case WHEN @pre_score=tmp.score THEN @k ELSE @j END) as rank,
	@pre_score:=tmp.score as pre_score
FROM
(
-- 成绩排序
SELECT * FROM student ORDER BY score DESC
) tmp,
-- @k 表示最终的排名(相同值时序号相同） 
-- @j 表示顺序排名 
-- @pre_score上一次排序值
(SELECT @k:=0, @j:=0, @pre_score:=0) xscore;

运行结果如下：

2、只要数据有相同的排名就一样，排名依次排序（1,2,2,3,3,4,5.....）

SELECT
	obj.id,
	obj.score,
	@rownum := @rownum + 1 AS rownum
FROM
	(
		SELECT
			id,
			score
		FROM
			student
		ORDER BY
			score DESC
	) AS obj,
	(SELECT @rownum := 0) r;

运行结果

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

SELECT
	obj.id,
	obj.score,
	CASE
WHEN @rowtotal = obj.score THEN
	@rownum
WHEN @rowtotal := obj.score THEN
	@rownum := @rownum + 1
WHEN @rowtotal = 0 THEN
	@rownum := @rownum + 1
END AS rownum
FROM
	(
		SELECT
			id,
			score
		FROM
			student
		ORDER BY
			score DESC
	) AS obj,
	(SELECT @rownum := 0, @rowtotal := NULL) r;

 

跟第一条sql的结果相对比你会发现，分数相同的排名也相同，并且最后一名的名次由第6名变成了第4名；

3、获取分数平均值的名次， 比如10个班级的平均分，按照班级名称排序，后面跟着名次。

SELECT
	A.*, @rank :=@rank + 1 AS pm
FROM
	(
		SELECT
			class,
			AVG(score) AS avgs
		FROM
			student
		GROUP BY
			class
		ORDER BY
			avgs DESC
	) A,
	(SELECT @rank := 0) B;

 

 

运行结果：