使用SQL查询出每门课程的成绩均大于80分的学生姓名

灰太狼 2020-11-01 08:47 727阅读 0赞

**题目：** 有一个student表，表中记录一个班级所有学生的课程和成绩，每个学生有多门课程，不同的学生选修的课程可能不一样。尝试写一条SQL语句查询出所有课程成绩大于80分的所有学生姓名。  
**表结构和表数据如下：**

-- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `score` double NOT NULL,
      `name` varchar(50) DEFAULT NULL,
      `course` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES ('1', '80', '张飞', '语文');
    INSERT INTO `student` VALUES ('2', '76', '张飞', '数学');
    INSERT INTO `student` VALUES ('3', '88', '刘备', '语文');
    INSERT INTO `student` VALUES ('4', '95', '刘备', '数学');
    INSERT INTO `student` VALUES ('5', '86', '关羽', '语文');
    INSERT INTO `student` VALUES ('6', '72', '关羽', '数学');
    INSERT INTO `student` VALUES ('7', '78', '关羽', '英语');
    INSERT INTO `student` VALUES ('8', '80', '诸葛亮', '语文');
    INSERT INTO `student` VALUES ('9', '85', '诸葛亮', '物理');
    INSERT INTO `student` VALUES ('10', '88', '诸葛亮', '化学');

![student表数据][student]

**方法一：逆向思维解答**  
先查出有课程成绩小于80分的学生姓名，然后用not in 或 not exists 方法进行排除。

//not in
    SELECT DISTINCT a.name
    FROM student a
    WHERE a.name NOT IN(SELECT DISTINCT b.name FROM student b WHERE b.score < 80)
    
    //not exists
    SELECT DISTINCT a.name
    FROM student a
    WHERE NOT EXISTS(SELECT 1 FROM student b WHERE b.score < 80 
    AND a.name = b.name)

**查询结果**

name
    刘备
    诸葛亮

**说明：**  
exists （sql 返回结果集，为真）  
not exists (sql 不返回结果集，为真）  
exists 与 in 最大的区别在于 in引导的子句只能返回一个字段

**方法2：使用Having**

SELECT s.name
    FROM student s
    GROUP BY s.name
    Having MIN(s.score)>=80

**查询结果**

name
    刘备
    诸葛亮

**说明：**  
SQL使用HAVING子句来指定一组行或聚合的过滤条件。  
HAVING子句通常与GROUP BY子句一起使用，以根据指定的条件过滤分组。如果省略GROUP BY子句，则HAVING子句的行为与WHERE子句类似。  
HAVING子句将过滤条件应用于每组分行，而WHERE子句将过滤条件应用于每个单独的行。

[student]: /images/1604220379353.png