MySQL如何查询同时选修1000和1001两门课程的学生记录

错误写法原因 #

• 写法1逻辑不成立：WHERE ClassNum = '1000' AND ClassNum = '1001'要求单条选课记录的课程编号同时等于两个不同值，不可能满足，因此无返回结果。
• 写法2缺少筛选条件：IN仅过滤出选修任意一门目标课程的学生，没有判断是否同时选修两门，因此返回结果范围过大。

实现方案 #

方案1：返回符合条件学生的完整两条选课记录 #

先通过子查询定位同时选修两门课的学生ID，再关联查询获取对应选课记录：

SELECT s.StudentID, s.StudentName, r.ClassNum
FROM STUDENT s
INNER JOIN REGISTRATION r ON s.StudentID = r.StudentID
WHERE s.StudentID IN (
    SELECT StudentID
    FROM REGISTRATION
    WHERE ClassNum IN (1000, 1001)
    GROUP BY StudentID
    HAVING COUNT(DISTINCT ClassNum) = 2
)
AND r.ClassNum IN (1000, 1001);

执行结果：

1 | John   | 1000
1 | John   | 1001

方案2：合并输出指定格式 #

使用MySQL的GROUP_CONCAT函数实现合并输出：

SELECT 
    s.StudentID,
    GROUP_CONCAT(DISTINCT r.ClassNum ORDER BY r.ClassNum SEPARATOR ' & ') AS ClassNum
FROM STUDENT s
INNER JOIN REGISTRATION r ON s.StudentID = r.StudentID
WHERE r.ClassNum IN (1000, 1001)
GROUP BY s.StudentID
HAVING COUNT(DISTINCT r.ClassNum) = 2;

执行结果：

1 | 1000 & 1001

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