给定一个包含班级和学生的表格，需要找到学生的差异。

Question

我有一个class_students表，在其中给定两个类in，我需要找到学生中的差异。这是我用过的sql小提琴，http://sqlfiddle.com/#!9/dba48e/11这里有任何帮助。

Answer 1

例如，使用

SELECT student_id
FROM class_students
GROUP BY student_id
HAVING SUM(class_id = 1) > 0
   AND SUM(class_id != 1) = 0

更多的信息是

SELECT student_id, GROUP_CONCAT(DISTINCT class_id) classes_in
FROM class_students
GROUP BY student_id
HAVING COUNT(DISTINCT class_id) < ( SELECT COUNT(DISTINCT class_id)
                                    FROM class_students )

PS。建议--将(class_id, student_id) (或按向后字段顺序)定义为唯一/主索引--它将禁止重复记录。

Answer 2

您可以使用NOT IN而不是减号运算符来获得预期的结果，因为MySQL没有负运算符。

SELECT a.student_id
FROM `class_students` a 
WHERE a.class_id = '1' 
AND a.student_id NOT IN (
    SELECT b.student_id
    FROM `class_students` b 
    WHERE b.class_id = '2'
)

请找到working demo on db<>fiddle

或者使用LEFT JOIN作为

SELECT a.student_id
FROM `class_students` a 
LEFT JOIN `class_students` b ON b.student_id = a.student_id AND b.class_id = '2'
WHERE a.class_id = '1' AND b.student_id IS NULL

Working demo on db<>fiddle

Answer 3

SELECT  student_id AS student,
        (1 + 2) - class_id AS "is not in class"
    FROM  class_students
    WHERE  class_id IN (1,2)
    GROUP BY  student_id
    HAVING  COUNT(*) < 2;

返回

student   is not in class
1         2
2         1

(建议您不要在学生和班级中使用相同的数字。)