-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`score` double(12, 4) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, '语文', '王一', '001', 99.0000);
INSERT INTO `score` VALUES (2, '语文', '周二', '002', 98.0000);
INSERT INTO `score` VALUES (3, '语文', '徐三', '003', 97.0000);
INSERT INTO `score` VALUES (4, '数学', '王一', '001', 91.0000);
INSERT INTO `score` VALUES (5, '数学', '周二', '002', 96.0000);
INSERT INTO `score` VALUES (6, '数学', '徐三', '003', 92.0000);
INSERT INTO `score` VALUES (7, '英语', '王一', '001', 76.0000);
INSERT INTO `score` VALUES (8, '英语', '周二', '002', 66.0000);
INSERT INTO `score` VALUES (9, '英语', '徐三', '003', 86.0000);
![](https://i-blog.csdnimg.cn/direct/6c71e2d0561c40829380e914166d279a.png)
SELECT
s.NO,
s.NAME,
s.SUBJECT,
MAX(s.score) '最高分'
FROM
( SELECT NO, NAME, SUBJECT, score FROM score HAVING 1 = 1 ORDER BY score.score DESC ) s
WHERE
1 = 1
GROUP BY
SUBJECT
![](https://i-blog.csdnimg.cn/direct/0771564abb1b49ae9a98b0c52f654031.png)