mysql机试题笔记425

题目

Sql题，有一个学生信息表， stu_info_table(stu_id, name, class)
和一张学生成绩表 stu_score_table(stu_id, course_id, score)
和一张课程表 stu_course(course_id, course_name)

因为有可能存在挂科，所以在学生成绩表中，
一个学生id+一个课程id可能会有多门成绩。
请查询，选修完“语文、数学、英语”所有课程 且
 没有选修除“语文、数学、英语”外其他任何课程 
  的学生姓名。

1
2
3
4
5
6
7
8
9
10

解法1，使用子查询和NOT EXISTS

先检查每个学生是否选修了所有三门指定的课程（语文、数学、英语），并且确保他们没有选修其他任何课程

SELECT si.name  
FROM stu_info_table si  
WHERE   
    -- 确保学生选修了所有三门课程  
    (SELECT COUNT(DISTINCT sc.course_id)   
     FROM stu_score_table sc   
     JOIN stu_course c ON sc.course_id = c.course_id   
     WHERE sc.stu_id = si.stu_id
      AND c.course_name IN ('语文', '数学', '英语')) = 3  
    AND  
    -- 确保学生没有选修其他课程  
    NOT EXISTS (  
        SELECT 1   
        FROM stu_score_table sc   
        JOIN stu_course c ON sc.course_id = c.course_id   
        WHERE sc.stu_id = si.stu_id   
        AND c.course_name NOT IN ('语文', '数学', '英语')  
    );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

解法2 使用LEFT JOIN/IS NULL的方式

先创建一个子查询ns来找出选修了非指定三门课程的学生ID。然后，我们将这个子查询通过LEFT JOIN连接到stu_info_table，并通过ns.stu_id IS NULL来过滤出那些没有选修其他课程的学生。同时在WHERE子句中检查了学生是否选修了所有三门指定的课程

SELECT si.name  
FROM stu_info_table si  
LEFT JOIN (  
    SELECT stu_id  
    FROM stu_score_table sc  
    JOIN stu_course c ON sc.course_id = c.course_id  
    WHERE c.course_name NOT IN ('语文', '数学', '英语')  
    GROUP BY stu_id  
) ns ON si.stu_id = ns.stu_id  
WHERE   
    (SELECT COUNT(DISTINCT sc.course_id)   
     FROM stu_score_table sc   
     JOIN stu_course c ON sc.course_id = c.course_id   
     WHERE sc.stu_id = si.stu_id AND c.course_name IN ('语文', '数学', '英语')) = 3  
    AND ns.stu_id IS NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15