MySQL多表查询：获取班级活跃课程完全匹配的学生姓名

这个精确匹配的需求其实核心要抓两个关键点：既要保证班级的所有活跃课程都在你传入的列表里，又要保证班级的活跃课程数量和传入列表的长度完全一致——缺了任何一个条件，都会出现你说的「把有额外课程的学生也返回」的问题。

我给你两种可行的SQL写法，你可以根据自己的习惯选择：

方法一：用分组聚合+HAVING子句筛选符合条件的班级 #

这种方法先通过子查询找出所有符合条件的班级，再关联学生表获取姓名，逻辑清晰且性能不错：

SELECT s.name AS student_name
FROM students s
INNER JOIN (
    -- 子查询：找出活跃课程恰好匹配传入列表的班级
    SELECT c.class_id
    FROM courses c
    WHERE c.active = TRUE -- 只考虑活跃课程
    GROUP BY c.class_id
    HAVING 
        -- 条件1：班级里没有不在传入列表中的活跃课程
        SUM(CASE WHEN c.name NOT IN ('foo', 'bar') THEN 1 ELSE 0 END) = 0
        -- 条件2：班级的活跃课程数量和传入列表的长度完全一致
        AND COUNT(*) = 2
) eligible_classes ON s.class_id = eligible_classes.class_id;

代码解释： #

• SUM(CASE...) = 0：遍历班级的每一门活跃课程，如果有课程不在传入列表里，就会累加1，最终总和为0就说明所有课程都在列表中。
• COUNT(*) = 2：确保班级的活跃课程数量正好等于你传入的数组长度（这里是2，对应['foo','bar']），避免出现「班级包含传入的所有课程，但还多了其他课程」的情况。

方法二：用双重NOT EXISTS做存在性检查 #

这种写法逻辑更直观，适合喜欢用存在性判断的场景：

SELECT s.name AS student_name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM classes cls
    WHERE cls.id = s.class_id
    -- 先检查班级活跃课程数量和传入列表一致
    AND (SELECT COUNT(*) FROM courses c WHERE c.class_id = cls.id AND c.active = TRUE) = 2
    -- 再检查班级没有任何不在传入列表里的活跃课程
    AND NOT EXISTS (
        SELECT 1
        FROM courses c
        WHERE c.class_id = cls.id
          AND c.active = TRUE
          AND c.name NOT IN ('foo', 'bar')
    )
);

适配Ruby函数的安全写法 #

在Ruby里使用时，一定要注意避免SQL注入，不要直接把数组拼进SQL字符串，要用参数占位符：

def get_student_names_whose_courses_are(active_course_names)
  course_count = active_course_names.length
  # 用占位符?来传递参数，数据库驱动会自动处理数组和数值的转义
  sql = <<~SQL
    SELECT s.name AS student_name
    FROM students s
    INNER JOIN (
        SELECT c.class_id
        FROM courses c
        WHERE c.active = TRUE
        GROUP BY c.class_id
        HAVING 
            SUM(CASE WHEN c.name NOT IN (?) THEN 1 ELSE 0 END) = 0
            AND COUNT(*) = ?
    ) eligible_classes ON s.class_id = eligible_classes.class_id
  SQL

  # 假设你用ActiveRecord，用select_values获取所有学生姓名的数组
  ActiveRecord::Base.connection.select_values(sql, nil, [active_course_names, course_count])
end

这样不管你传入什么课程名称数组，都能安全且准确地返回符合条件的学生姓名啦。

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