一、实现SQL语句
-- 数据展示
SELECT * from student_grade t WHERE
t.id in (
-- 确定科目的最大值行id
SELECT (SELECT a.id FROM student_grade a WHERE a.course = s.course ORDER BY a.grade desc LIMIT 1) id
-- 获取所有科目
from (SELECT DISTINCT t.course FROM `student_grade` t) s
)
二、测试用例
1. 数据创建
CREATE TABLE `student_grade` (
`id` int(11) NOT NULL COMMENT 'id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
`course` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '科目',
`grade` float(255, 0) DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student_grade
-- ----------------------------
INSERT INTO `student_grade` VALUES (1, '张三', '数学', 69);
INSERT INTO `student_grade` VALUES (3, '张三', '语文', 67);
INSERT INTO `student_grade` VALUES (4, '李四', '数学', 58);
INSERT INTO `student_grade` VALUES (5, '王五', '语文', 70);
INSERT INTO `student_grade` VALUES (6, '小米', '语文', 71);
2. sql执行结果
三、注意
1.当grade最大值存在多个相同时,取值可能变得随机。