需求: 查出每个班级分数前3高的学生 CREATE TABLE `stu` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT '' COMMENT '学生名', `class` int(11) DEFAULT NULL COMMENT '班级', `score` int(11) DEFAULT NULL COMMENT '分数', PRIMARY KEY (`ID`), UNIQUE KEY `unique_idx_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='学生'; INSERT INTO `mytest`.`stu` (`ID`, `name`, `class`, `score`) VALUES ('1', 'a', '1', '88'); INSERT INTO `mytest`.`stu` (`ID`, `name`, `class`, `score`) VALUES ('2', 'b', '1', '99'); INSERT INTO `mytest`.`stu` (`ID`, `name`, `class`, `score`) VALUES ('3', 'c', '2', '99'); INSERT INTO `mytest`.`stu` (`ID`, `name`, `class`, `score`) VALUES ('4', 'd', '2', '97'); INSERT INTO `mytest`.`stu` (`ID`, `name`, `class`, `score`) VALUES ('5', 'e', '2', '97'); INSERT INTO `mytest`.`stu` (`ID`, `name`, `class`, `score`) VALUES ('6', 'f', '2', '98'); INSERT INTO `mytest`.`stu` (`ID`, `name`, `class`, `score`) VALUES ('7', 'g', '2', '55'); SELECT * FROM stu WHERE `name` IN ( SELECT a.`name` FROM stu a LEFT JOIN stu b ON a.class = b.class AND a.score < b.score GROUP BY a.`name` HAVING COUNT(DISTINCT b.`name`) <= 2 ) ORDER BY class, score DESC 输出: ID name class score 2 b 1 99 1 a 1 88 3 c 2 99 6 f 2 98 4 d 2 97 5 e 2 97
LeetCode_db:查出每个班级分数前3高的学生
最新推荐文章于 2023-08-22 22:09:52 发布