mysql排行榜并列与不并列

分数表：

一、普通并列排行

SELECT
	*, (
		SELECT
			count(DISTINCT score)
		FROM
			scores AS b
		WHERE
			a.score < b.score
	) + 1 AS rank
FROM
	scores AS a
ORDER BY
	`rank`

 

 

mysql 5.7

 

SELECT
	Score,
	dense_rank () over (ORDER BY Score DESC) 'Rank'
FROM
	score;

二、高级并列排行

SELECT
	id,
	score,
	rank
FROM
	(
		SELECT
			id,
			score,
			@curRank :=
		IF (
			@prevRank = score,
			@curRank,
			@incRank
		) AS rank,
		@incRank := @incRank + 1,
		@prevRank := score
	FROM
		scores p,
		(
			SELECT
				@curRank := 0,
				@prevRank := NULL,
				@incRank := 1
		) r
	ORDER BY
		score
	) s

 

 

三、不并列排行

SELECT
	id,
	score ,@rank := @rank + 1 AS rank
FROM
	(
		SELECT
			*
		FROM
			scores
		ORDER BY
			score DESC
	) AS obj,
	(SELECT @rank := 0) r