SQL笔记：分组排序 在一张包含学生ID、课程、成绩的表中，检索出每门课程的前三名

表定义 及 测试数据

CREATE TABLE `score_info` (
  `student_id` int NOT NULL AUTO_INCREMENT,
  `category` varchar(255) DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `score_info` VALUES ('1', '语', '99');
INSERT INTO `score_info` VALUES ('2', '数', '32');
INSERT INTO `score_info` VALUES ('3', '外', '88');
INSERT INTO `score_info` VALUES ('4', '语', '89');
INSERT INTO `score_info` VALUES ('5', '数', '57');
INSERT INTO `score_info` VALUES ('6', '外', '67');
INSERT INTO `score_info` VALUES ('7', '语', '72');
INSERT INTO `score_info` VALUES ('8', '数', '48');
INSERT INTO `score_info` VALUES ('9', '外', '53');
INSERT INTO `score_info` VALUES ('10', '语', '96');
INSERT INTO `score_info` VALUES ('11', '数', '69');
INSERT INTO `score_info` VALUES ('12', '数', '39');
INSERT INTO `score_info` VALUES ('13', '外', '93');
INSERT INTO `score_info` VALUES ('14', '数', '39');
INSERT INTO `score_info` VALUES ('15', '外', '93');
INSERT INTO `score_info` VALUES ('16', '语', '96');
INSERT INTO `score_info` VALUES ('17', '数', '39');
INSERT INTO `score_info` VALUES ('18', '语', '96');
INSERT INTO `score_info` VALUES ('19', '数', '88');
INSERT INTO `score_info` VALUES ('20', '语', '77');
INSERT INTO `score_info` VALUES ('21', '数', '54');
INSERT INTO `score_info` VALUES ('22', '外', '73');
INSERT INTO `score_info` VALUES ('23', '数', '96');

查询结果

• 根据分数排名 分数相同 按先后排序

SELECT
	t.student_id,
	t.category,
	t.score,
	t.rowNumber
FROM
	(
		SELECT
			s.*,@rowNumber := (
				CASE
				WHEN @categoryName = s.category THEN
					@rowNumber + 1
				ELSE
					1
				END
			) rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfter
		FROM
			score_info s,
			(
				SELECT
					@rowNumber := 0 ,@categoryName := '--'
			) r
		ORDER BY
			category DESC,
			score DESC
	) t
WHERE
	t.rowNumber <= 3

• 根据分数排民分数相同并列

SELECT
	t.category,
	t.score,
	t.studentIds,
	t.rowNumber
FROM
	(
		SELECT
			s.category,
			s.score,
			GROUP_CONCAT(s.student_id) studentIds ,@rowNumber := (
				CASE
				WHEN @categoryName = s.category THEN
					@rowNumber + 1
				ELSE
					1
				END
			) rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfter
		FROM
			score_info s,
			(
				SELECT
					@rowNumber := 0 ,@categoryName := '--'
			) r
		GROUP BY
			category,
			score
		ORDER BY
			category DESC,
			score DESC
	) t
WHERE
	t.rowNumber <= 3

思路

要在一张包含学生ID、成绩、课程的表中检索出每门课程的前三名

1）、分组课程，再按成绩排序

SELECT
	s.*,@rowNumber :=@rowNumber + 1 rowNumber
FROM
	score_info s,
	(SELECT @rowNumber := 0) r
ORDER BY
	category DESC,
	score DESC;

2）、要根据不同的课程各自排序，要先获取到“上一条数据”的课程类型

SELECT
	s.*,@rowNumber :=@rowNumber + 1 rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfter
FROM
	score_info s,
	(
		SELECT
			@rowNumber := 0 ,@categoryName := '--'
	) r
ORDER BY
	category DESC,
	score DESC;

3）、已经获取到上一条数据的课程类型，那就可以用当前课程比对上一条课程，判断是否重新排序

SELECT
	s.*,@rowNumber := (
		CASE
		WHEN @categoryName = s.category THEN
			@rowNumber + 1
		ELSE
			1
		END
	) rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfter
FROM
	score_info s,
	(
		SELECT
			@rowNumber := 0 ,@categoryName := '--'
	) r
ORDER BY
	category DESC,
	score DESC;

4）、分组课程和成绩，实现 课程相同 且 成绩相同的排名并列（根据需求 自行决定是否需要并列）

SELECT
			s.category,
			s.score,
			GROUP_CONCAT(s.student_id) studentIds ,@rowNumber := (
				CASE
				WHEN @categoryName = s.category THEN
					@rowNumber + 1
				ELSE
					1
				END
			) rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfter
		FROM
			score_info s,
			(
				SELECT
					@rowNumber := 0 ,@categoryName := '--'
			) r
		GROUP BY
			category,
			score
		ORDER BY
			category DESC,
			score DESC

5）、已经可以获取到各科成绩排名，要获取前三名 可以直接where rowNumber<=3 ，即可实现检索各科前三名

SELECT
	t.category,
	t.score,
	t.studentIds,
	t.rowNumber
FROM
	(
		SELECT
			s.category,
			s.score,
			GROUP_CONCAT(s.student_id) studentIds ,@rowNumber := (
				CASE
				WHEN @categoryName = s.category THEN
					@rowNumber + 1
				ELSE
					1
				END
			) rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfter
		FROM
			score_info s,
			(
				SELECT
					@rowNumber := 0 ,@categoryName := '--'
			) r
		GROUP BY
			category,
			score
		ORDER BY
			category DESC,
			score DESC
	) t
WHERE
	t.rowNumber <= 3

搜索公众号：【爪哇日记】关注最新文章