mysql实现分组排序（取前几条数据）

有一个score表，查询每科课程成绩排前3的课程id、学生名称、学生分数。

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(10) NOT NULL,
  `student_name` varchar(10) DEFAULT NULL,
  `subject_id` int(10) DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of score
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES (1, 'yiqin', 2, 85);
INSERT INTO `score` VALUES (2, 'sengseng', 2, 80);
INSERT INTO `score` VALUES (3, 'tao', 3, 76);
INSERT INTO `score` VALUES (4, '李四', 2, 82);
INSERT INTO `score` VALUES (7, 'lin', 1, 92);
INSERT INTO `score` VALUES (8, '王五', 2, 88);
INSERT INTO `score` VALUES (9, '王五', 3, 70);
INSERT INTO `score` VALUES (10, '赵六', 1, 99);
INSERT INTO `score` VALUES (11, '赵六', 2, 82);
INSERT INTO `score` VALUES (12, '赵六', 3, 70);
INSERT INTO `score` VALUES (13, 'yi', 4, 80);
INSERT INTO `score` VALUES (14, 'junjun', 2, 83);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

 

mysql语句：

select s.subject_id,
    s.student_name AS '学生名称',
    s.score AS '分数' 
FROM
    score s
WHERE
    (
    SELECT count(*) FROM score s2 WHERE  s.subject_id =s2.subject_id and s2.score > s.score
    ) < 3 
ORDER BY s.subject_id,s.score DESC

解释一下这个语句：

首先要清楚自连接，以课程id=2为例：

然后过程大概是这样的：

取S表第1条数据yiqin 2 85分别与S2表中课程id=2的分数作比较，如果S2表中的分数大于85，则计数，count为1，所以这个学生是该课程的第2名，符合条件

取S表第2条数据sengseng 2 80分别与S2表中课程id=2的分数作比较，如果S2表中的分数大于80，则count为5，所以这个学生是该课程的第6名，不符合条件

取S表第3条数据李四 2  82分别与S2表中课程id=2的分数作比较，如果S2表中的分数大于82，则count为3，所以这个学生是该课程的第4名，不符合条件。表中赵六也是82分，如果要求是每科课程成绩排前4，那么李四和赵六都符合条件，会返回5条数据。

......

 

扩展一下，还有一张Teacher表，查询每个老师所教每科课程成绩排前3的老师名称、课程id、学生名称、学生分数。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` bigint(20) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `teacher_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of teacher
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES (2, 1, 'Sunny');
INSERT INTO `teacher` VALUES (3, 2, 'Sunny');
INSERT INTO `teacher` VALUES (4, 3, 'Tom');
INSERT INTO `teacher` VALUES (5, 4, 'Tom');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

mysql语句：

SELECT
    t.teacher_name	AS '教师名称',
		t.subject_id AS '课程id',
    s.student_name AS '学生名称',
    s.score AS '分数' 
FROM
    score s
    JOIN teacher t ON t.subject_id = s.subject_id 
WHERE
    (
    SELECT count(*) FROM score s2 WHERE  s.subject_id =s2.subject_id and s2.score > s.score
    ) < 3 
ORDER BY s.subject_id,s.score DESC