Oracle数据库中如何查询选修了John全部所选课程的学生姓名

你目前的写法核心问题是使用了IN关键字，该关键字仅判断字段是否存在于目标集合中，只要学生选修了John的任意一门课就会满足条件，无法要求覆盖全部课程，属于典型的关系除法匹配场景。
另外你的第二个查询中额外关联了CLASS表属于冗余操作，仅匹配课程编号不需要用到课程表的字段，可以直接去掉。

方案1：分组计数匹配（写法简单易理解） #

核心逻辑：统计每个非John学生选修的、属于John选课范围的课程数量，只要该数量等于John的总选课数，就说明覆盖了全部John的选课。

SELECT s.SNAME
FROM STUDENT s
JOIN ATTENDANCE a ON s.SNO = a.SNO
WHERE s.SNAME <> 'John'
  AND a.CNO IN (
    SELECT ja.CNO
    FROM ATTENDANCE ja
    JOIN STUDENT js ON ja.SNO = js.SNO
    WHERE js.SNAME = 'John'
)
GROUP BY s.SNO, s.SNAME
HAVING COUNT(DISTINCT a.CNO) = (
    SELECT COUNT(DISTINCT ja.CNO)
    FROM ATTENDANCE ja
    JOIN STUDENT js ON ja.SNO = js.SNO
    WHERE js.SNAME = 'John'
);

注意：如果ATTENDANCE表已经保证同一个学生同一个课程只有一条记录，可以去掉DISTINCT关键字提升性能。

方案2：双重NOT EXISTS（更严谨的关系除法写法） #

核心逻辑：不存在任何一门John选过的课，是当前学生没有选的，符合你要求的全覆盖逻辑，即使存在重复选课记录也不会出错，不需要硬编码任何数值。

SELECT DISTINCT s.SNAME
FROM STUDENT s
WHERE s.SNAME <> 'John'
  AND NOT EXISTS (
    -- 遍历John的所有选课
    SELECT ja.CNO
    FROM ATTENDANCE ja
    JOIN STUDENT js ON ja.SNO = js.SNO
    WHERE js.SNAME = 'John'
      AND NOT EXISTS (
        -- 检查当前学生是否选了这门课
        SELECT 1
        FROM ATTENDANCE a
        WHERE a.SNO = s.SNO
          AND a.CNO = ja.CNO
      )
);

内容的提问来源于stack exchange，提问作者ChronoKeeper