求选课时与Alice完全一致的学生列表的SQL实现咨询

我来帮你搞定这个需求！你的目标是筛选出和Alice选课完全一致的学生——也就是既要保证这些学生选的每一门课Alice都选了，同时Alice选的每一门课这些学生也都选了。先看看你原来的SQL逻辑哪里有漏洞，再给你两个可靠的解法。

原SQL的问题分析 #

你写的查询只实现了一半逻辑：它确保了目标学生没有选Alice没选过的课（也就是学生的选课是Alice选课的子集），但没有验证反过来的情况——Alice选的课，目标学生是不是都选了。举个例子：如果Alice选了数学、英语、物理三门课，而某个学生只选了数学和英语，你的原查询会错误地把这个学生也列出来，因为他没选Alice没选的课，但他的选课和Alice并不完全一致。

解法一：用聚合函数对比选课匹配度 #

这个方法通过统计选课数量和匹配数量来判断是否完全一致，逻辑清晰易懂：

-- 先获取Alice的选课列表和选课总数
WITH alice_courses AS (
    SELECT code 
    FROM enrol 
    WHERE sid = (SELECT sid FROM student WHERE sname = 'Alice')
),
alice_course_count AS (
    SELECT COUNT(*) AS total_courses FROM alice_courses
)
SELECT s.email
FROM student s
JOIN enrol e ON s.sid = e.sid
CROSS JOIN alice_course_count acc
-- 关联Alice的选课，找出重叠的课程
LEFT JOIN alice_courses ac ON e.code = ac.code
WHERE s.sname <> 'Alice'
GROUP BY s.sid, s.email, acc.total_courses
-- 两个条件同时满足：
-- 1. 学生的选课总数等于Alice的选课总数
-- 2. 学生选的课中，和Alice重叠的数量也等于Alice的选课总数
HAVING COUNT(*) = acc.total_courses
   AND COUNT(ac.code) = acc.total_courses;

解法二：用双重NOT EXISTS验证双向子集 #

这个方法通过两层NOT EXISTS来确保双向的包含关系：学生的所有课都在Alice的选课里，且Alice的所有课都在学生的选课里：

SELECT s1.email
FROM student s1
WHERE s1.sname <> 'Alice'
  -- 验证：学生选的每一门课，Alice都选了
  AND NOT EXISTS (
      SELECT 1 
      FROM enrol e1
      WHERE e1.sid = s1.sid
        AND NOT EXISTS (
            SELECT 1 
            FROM enrol e
            JOIN student s ON s.sid = e.sid
            WHERE s.sname = 'Alice' AND e.code = e1.code
        )
  )
  -- 验证：Alice选的每一门课，学生都选了
  AND NOT EXISTS (
      SELECT 1 
      FROM enrol e
      JOIN student s ON s.sid = e.sid
      WHERE s.sname = 'Alice'
        AND NOT EXISTS (
            SELECT 1 
            FROM enrol e1
            WHERE e1.sid = s1.sid AND e1.code = e.code
        )
  );

这两种方法都能准确筛选出和Alice选课完全一致的学生，你可以根据自己的习惯选择使用～

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