sql笔试题：每个班级中成绩最高的女生分别是谁？

昨天笔试遇到一个写sql的题目

 

题目是，每个班级中成绩最高的女生分别是谁？

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `name` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `num` int NULL DEFAULT NULL,
  `class` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` tinyint(1) NULL DEFAULT NULL,
  `score` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('张三', 1, '一班', 0, 97);
INSERT INTO `student` VALUES ('李四', 2, '一班', 0, 99);
INSERT INTO `student` VALUES ('小丽', 3, '一班', 1, 96);
INSERT INTO `student` VALUES ('王二', 1, '二班', 0, 95);
INSERT INTO `student` VALUES ('麻子', 2, '二班', 0, 99);
INSERT INTO `student` VALUES ('小娜', 3, '二班', 1, 87);
INSERT INTO `student` VALUES ('小萱', 4, '一班', 1, 98);
INSERT INTO `student` VALUES ('小媛', 4, '二班', 1, 98);
INSERT INTO `student` VALUES ('小四', 4, '一班', 1, 99);

SET FOREIGN_KEY_CHECKS = 1;

答案

SELECT NAME,a.class,a.score
FROM 
	(SELECT NAME,score,class FROM student1 WHERE sex = 1) a 
LEFT JOIN
	(SELECT MAX(score)MAX,class 
FROM 
	(SELECT NAME,score,class FROM student1 WHERE sex = 1) tab 
GROUP BY class) b 
ON a.class = b.class
WHERE score = MAX