SQL查询学生表中每课大于85分的同学名称

淡淡的烟草味﹌ 2022-09-12 07:54 151阅读 0赞

SQL语句

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for t_student
    -- ----------------------------
    DROP TABLE IF EXISTS `t_student`;
    CREATE TABLE `t_student`  (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `course` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `score` int NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of t_student
    -- ----------------------------
    INSERT INTO `t_student` VALUES (1, 'mary', '语文', 87);
    INSERT INTO `t_student` VALUES (2, 'mary', '数学', 75);
    INSERT INTO `t_student` VALUES (3, 'jeky', '语文', 76);
    INSERT INTO `t_student` VALUES (4, 'jeky', '数学', 90);
    INSERT INTO `t_student` VALUES (5, 'lisa', '语文', 88);
    INSERT INTO `t_student` VALUES (6, 'lisa', '数学', 100);
    INSERT INTO `t_student` VALUES (7, 'lisa', '英语', 90);
    
    SET FOREIGN_KEY_CHECKS = 1;

解析：  
首先查出每课小于85分的人数，然后再从总人数里面出去

select DISTINCT `name` from t_student where name not in(select distinct name from t_student where score<=85);