sql - 查询以查找未通过所有给定科目的学生

Question

我试图通过 PostgreSQL 查询找出一组科目中每个科目都不及格的学生。

如果学生在该科目的至少一门类(class)中的非零分数 < 50，则该科目不及格。我想找到所有科目都不及格的学生 Relevant_subjects。
注意:学生每门类(class)可以有多个记录。

SELECT People.name
FROM 
    Relevant_subjects
    JOIN Courses on (Courses.subject = Relevant_subjects.id)
    JOIN Course_enrolments on (Course_enrolments.course = Courses.id)
    JOIN Students on (Students.id = Course_enrolments.student)
    JOIN People on (People.id = Students.id)
WHERE
    Course_enrolments.mark is not null AND
    Course_enrolments.mark < 50 AND
;

使用上面的代码，我得到了未通过任何 Relevant_subjects 的学生，但我想要的结果是让未通过所有 Relevant_subjects 的学生。我该怎么做？

Answer 1

Students fail a subject if they have a not null mark < 50 for at least one course offering of the subject.

许多可能的方法之一:

SELECT id, p.name
FROM  (
   SELECT s.id
   FROM   students                s
   CROSS  JOIN relevant_subjects rs
   GROUP  BY s.id
   HAVING bool_and( EXISTS(
            SELECT -- empty list
            FROM   course_enrolments ce
            JOIN   courses           c  ON c.id = ce.course
            WHERE  ce.mark < 50  -- also implies NOT NULL
            AND    ce.student = s.id
            AND    c.subject = rs.id
            )
         ) -- all failed
   ) sub
JOIN   people p  USING (id);

1. 形成学生和相关科目的 Carthesian 积。

2. 按学生 (s.id) 汇总并过滤那些在 HAVING 子句中未通过 all 科目的人 bool_and()在相关的 EXISTS 子查询上测试每个学生-学科组合的至少一门这样的失败类(class)。

3. 加入 people 作为获取学生姓名的最后装饰步骤。我添加了 id 以获得唯一的结果(因为 names 可能不能保证是唯一的)。

根据实际的表定义、您的 Postgres 版本、基数和值分布，可能会有(很多)更高效的查询。

以relational-division为例在其核心。见:

• How to filter SQL results in a has-many-through relation

最有效的策略是尽可能早地在查询中排除尽可能多的学生——比如首先检查失败学生最少的科目。然后只处理剩下的学生等。

您的案例增加了待测对象的数量和身份未知/动态的具体困难。通常，递归 CTE 或类似的方法可以为此类问题提供最佳性能:

• SQL query to find a row with a specific number of associations