如何筛选所有考试成绩均低于50分或未参加考试的学生

表结构 #

student表 #

exam_result表 #

需求说明 #

筛选满足以下任一条件的学生：

• 所有考试成绩均低于50分
• 未参加任何考试

本例中符合条件的学生为id=1、2、4。

原有查询的问题 #

原有查询语句如下：

SELECT DISTINCT s.id, s.first_name, s.last_name
FROM university.student s
LEFT JOIN
    university.exam_result er ON
        s.id = er.student_id
WHERE er.mark < 50 OR er.mark IS NULL;

该语句错误返回了id=6的学生，原因是：只要学生有任意一门成绩低于50，就会被筛选出来，但id=6的学生仅一门成绩低于50，其余均高于50，不符合“所有成绩均低于50”的要求。

通用解决方案（兼容PostgreSQL和MariaDB） #

方法1：排除有合格成绩的学生 #

通过子查询找出所有存在成绩≥50的学生ID，然后从学生表中排除这些学生，剩下的就是符合要求的学生（包括未参加考试的）：

SELECT s.id, s.first_name, s.last_name
FROM university.student s
WHERE s.id NOT IN (
    SELECT DISTINCT er.student_id
    FROM university.exam_result er
    WHERE er.mark >= 50
);

方法2：分组后判断成绩范围 #

利用GROUP BY和HAVING子句，对学生分组后判断：要么没有考试记录，要么所有成绩都低于50：

SELECT s.id, s.first_name, s.last_name
FROM university.student s
LEFT JOIN university.exam_result er ON s.id = er.student_id
GROUP BY s.id, s.first_name, s.last_name
HAVING MAX(er.mark) < 50 OR MAX(er.mark) IS NULL;

两种方法都能正确返回id=1、2、4的学生，且均为标准SQL语法，可在PostgreSQL和MariaDB中直接运行。

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