时间戳

首页 > 解决方案 > 查询以查找未通过所有给定科目的学生

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

问题描述

我试图通过 PostgreSQL 查询找到一组科目中每个科目都未通过的学生。

如果学生在该科目的至少一门课程中的非空分 < 50 分，则该科目不及格。我想在这组科目中找到所有科目都未通过的学生Relevant_subjects。
注意：学生每门课程可以有多个记录。

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。我怎样才能做到这一点？

标签： sqlpostgresqlrelational-division

解决方案

如果学生在该科目的至少一门课程中的非空分 < 50 分，则该科目不及格。

许多可能的方法之一：

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_ bool_and()_EXISTS

  3. 加入people作为最后的整容步骤以获得学生姓名。我添加id以获得独特的结果（因为名称可能不保证是唯一的）。

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

这是一个以为核心的案例。看：

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

您的案例增加了要测试的对象的数量和身份是未知/动态的特定困难。通常，递归 CTE 或类似方法为此类问题提供最佳性能：

推荐阅读