表定义 及 测试数据
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');
查询结果
- 根据分数排名(分数相同 按先后排序)
SELECTt.student_id,t.category,t.score,t.rowNumberFROM(SELECTs.*,@rowNumber := (CASEWHEN @categoryName = s.category THEN@rowNumber + 1ELSE1END) rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfterFROMscore_info s,(SELECT@rowNumber := 0 ,@categoryName := '--') rORDER BYcategory DESC,score DESC) tWHEREt.rowNumber <= 3
- 根据分数排民(分数相同并列)
SELECTt.category,t.score,t.studentIds,t.rowNumberFROM(SELECTs.category,s.score,GROUP_CONCAT(s.student_id) studentIds ,@rowNumber := (CASEWHEN @categoryName = s.category THEN@rowNumber + 1ELSE1END) rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfterFROMscore_info s,(SELECT@rowNumber := 0 ,@categoryName := '--') rGROUP BYcategory,scoreORDER BYcategory DESC,score DESC) tWHEREt.rowNumber <= 3
思路
要在一张包含学生ID、成绩、课程的表中检索出每门课程的前三名
1)、分组课程,再按成绩排序
SELECTs.*,@rowNumber :=@rowNumber + 1 rowNumberFROMscore_info s,(SELECT @rowNumber := 0) rORDER BYcategory DESC,score DESC;
2)、要根据不同的课程各自排序,要先获取到“上一条数据”的课程类型
SELECTs.*,@rowNumber :=@rowNumber + 1 rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfterFROMscore_info s,(SELECT@rowNumber := 0 ,@categoryName := '--') rORDER BYcategory DESC,score DESC;
3)、已经获取到上一条数据的课程类型,那就可以用当前课程比对上一条课程,判断是否重新排序
SELECTs.*,@rowNumber := (CASEWHEN @categoryName = s.category THEN@rowNumber + 1ELSE1END) rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfterFROMscore_info s,(SELECT@rowNumber := 0 ,@categoryName := '--') rORDER BYcategory DESC,score DESC;
4)、分组课程和成绩,实现 课程相同 且 成绩相同的排名并列(根据需求 自行决定是否需要并列)
SELECTs.category,s.score,GROUP_CONCAT(s.student_id) studentIds ,@rowNumber := (CASEWHEN @categoryName = s.category THEN@rowNumber + 1ELSE1END) rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfterFROMscore_info s,(SELECT@rowNumber := 0 ,@categoryName := '--') rGROUP BYcategory,scoreORDER BYcategory DESC,score DESC
5)、已经可以获取到各科成绩排名,要获取前三名 可以直接where rowNumber<=3 ,即可实现检索各科前三名
SELECTt.category,t.score,t.studentIds,t.rowNumberFROM(SELECTs.category,s.score,GROUP_CONCAT(s.student_id) studentIds ,@rowNumber := (CASEWHEN @categoryName = s.category THEN@rowNumber + 1ELSE1END) rowNumber ,@categoryName categoryNameBefor ,@categoryName := category categoryNameAfterFROMscore_info s,(SELECT@rowNumber := 0 ,@categoryName := '--') rGROUP BYcategory,scoreORDER BYcategory DESC,score DESC) tWHEREt.rowNumber <= 3