查询学生课程最高分sql查询

最新推荐文章于 2024-06-14 10:30:09 发布

大王1024

最新推荐文章于 2024-06-14 10:30:09 发布

阅读量173

点赞数 2

分类专栏：面试问题文章标签： sql java 数据库

本文链接：https://blog.csdn.net/weixin_44485508/article/details/137605699

版权

面试问题专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据准备

# 学生表
CREATE TABLE `su_stu`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `su_stu` VALUES (1, '张三', 18);
INSERT INTO `su_stu` VALUES (2, '李四', 19);
INSERT INTO `su_stu` VALUES (3, '王五', 18);
# 课程表
CREATE TABLE `su_ke_cheng`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `su_ke_cheng` VALUES (1, '语文');
INSERT INTO `su_ke_cheng` VALUES (2, '数学');
INSERT INTO `su_ke_cheng` VALUES (3, '英语');

#成绩表
CREATE TABLE `su_score`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(11) NULL DEFAULT NULL,
  `ke_id` int(11) NULL DEFAULT NULL,
  `score` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `su_score` VALUES (1, 1, 1, 60);
INSERT INTO `su_score` VALUES (2, 1, 2, 65);
INSERT INTO `su_score` VALUES (3, 1, 3, 64);
INSERT INTO `su_score` VALUES (4, 2, 1, 70);
INSERT INTO `su_score` VALUES (5, 2, 2, 75);
INSERT INTO `su_score` VALUES (6, 2, 3, 76);
INSERT INTO `su_score` VALUES (7, 3, 1, 56);
INSERT INTO `su_score` VALUES (8, 3, 2, 92);
INSERT INTO `su_score` VALUES (9, 3, 3, 95);

查询课程最高分的学生

SELECT su_score.*  FROM su_score,(SELECT su_score.ke_id,MAX(su_score.score)as  score FROM su_score GROUP BY su_score.ke_id)as b WHERE su_score.ke_id=b.ke_id AND su_score.score=b.score