基于关联表筛选学生的MySQL多条件查询问题求助

表结构及数据 #

studentTable（学生表） #

studentCourseTable（学生选课关联表） #

需求描述 #

筛选出同时选修指定课程集合（如courseId 1和3）且未选修排除课程集合（如courseId 2）的学生，支持动态组合条件：

• 需选修1、2、3；
• 未选修1、2但选修3；
• 其他组合场景。

补充规则：学生可选修指定课程外的其他课程，只要不在排除列表中即可。例如要求选修(2,4)但未选修(3)，则选修(2,4,5)的学生符合要求，选修(2,3)的不符合。

尝试的错误SQL #

用户曾尝试以下SQL，但无法实现多条件筛选：

SELECT student.* 
FROM      studentTable AS s
LEFT JOIN studentCourseTable AS sc 
       ON sc.studentId = s.id
WHERE sc.studentId IN (1,3)
  AND sc.studentId NOT IN (2)

SELECT student.* 
FROM      studentTable AS s
LEFT JOIN studentCourseTable AS sc 
       ON sc.studentId = s.id 
      AND sc.courseId IN (1, 3) 
      AND sc.courseId IN (2)

解决方案 #

场景1：选修指定课程中任意一门且未选修排除课程 #

通过关联选课表+子查询排除，筛选出选了指定课程中至少一门，且未选排除课程的学生：

SELECT s.*
FROM studentTable s
JOIN studentCourseTable sc ON s.id = sc.studentId
WHERE sc.courseId IN (1, 3)
AND s.id NOT IN (SELECT studentId FROM studentCourseTable WHERE courseId = 2)
GROUP BY s.id, s.studentName

场景2：同时选修所有指定课程且未选修排除课程 #

通过分组统计指定课程的选修数量，确保学生选全了所有指定课程，同时排除选了排除课程的学生：

SELECT s.*
FROM studentTable s
JOIN studentCourseTable sc ON s.id = sc.studentId
WHERE sc.courseId IN (1, 3)
AND s.id NOT IN (SELECT studentId FROM studentCourseTable WHERE courseId = 2)
GROUP BY s.id, s.studentName
HAVING COUNT(DISTINCT sc.courseId) = 2 -- 此处数字需与指定课程的数量一致（示例中1、3共2门）

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