解决MySQL5和8的成绩排序问题

MySQL8.0排序问题

1. 创建表

CREATE TABLE `stu` (
  `id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



INSERT INTO `stu` VALUES (1, 'zs', 90);
INSERT INTO `stu` VALUES (2, 'ls', 90);
INSERT INTO `stu` VALUES (3, 'ww', 85);
INSERT INTO `stu` VALUES (4, 'zl', 80);
INSERT INTO `stu` VALUES (5, 'tq', 60);

2. 排序

ROW_NUMBER()：顺序排序——1、2、3、4、5

SELECT
	id,
	name,
	score,
	row_number ( ) over ( ORDER BY score desc ) as sort
FROM
	stu s 
ORDER BY
	score
	desc

RANK()：并列排序，跳过重复序号——1、1、3、4、5

SELECT
	id,
	name,
	score,
	rank ( ) over ( ORDER BY score desc ) as sort
FROM
	stu s 
ORDER BY
	score
	desc

DENSE_RANK()：并列排序，不跳过重复序号——1、1、2、3、4

SELECT
	id,
	name,
	score,
	dense_rank ( ) over ( ORDER BY score desc ) as sort
FROM
	stu s 
ORDER BY
	score
	desc

MySQL5.0排序问题

顺序排序——1、2、3、4、5

SELECT id, name,score, @curRank := @curRank + 1 AS rk
FROM stu, (
SELECT @curRank := 0
) r
ORDER BY score desc;

并列排序，跳过重复序号——1、1、3、4、5

SELECT
	id,
	NAME,
	score,
	rk 
FROM
	(
	SELECT
		id,
		NAME,
		score,
		@curRank :=
	IF
		( @prevRank = score, @curRank, @incRank ) AS rk,
		@incRank := @incRank + 1,
		@prevRank := score 
	FROM
		stu,
		( SELECT @curRank := 0, @prevRank := NULL, @incRank := 1 ) r 
	ORDER BY
	score DESC 
	) s;

并列排序，不跳过重复序号——1、1、2、3、4

SELECT
	id,
	NAME,
	score,
CASE
		WHEN @prevRank = score THEN
		@curRank 
		WHEN @prevRank := score THEN
		@curRank := @curRank + 1 
	END as rk
FROM
	stu,
	( SELECT @curRank := 0, @prevRank := NULL ) as rk
ORDER BY
	score desc