PostgreSQL -查询列出所有至少有一个分数与0不同的学生

Question

我正在研究PostgreSQL，我试图检索在某一特定事项中至少有一个分数与0不同的学生的列表，或者简单地过滤在特定事项中具有0的所有学生，这是我的DB结构：

Student {
id,
name,
other info..
}

Mark {
id,
student_id,
matter,
mark
}

例如

Student(1,"John"..)
Mark(1,1,'Mathematics',0);
Mark(2,1,'Mathematics',0);
Mark(3,1,'Mathematics',0);

Mark(4,1,'Physics',15);
Mark(5,1,'Physics',12);
Mark(6,1,'Physics',11);


Student(2,"Albert"..)

Mark(7,2,'Mathematics',0);
Mark(8,2,'Mathematics',17);
Mark(9,2,'Mathematics',0);

Mark(10,2,'Physics',0);
Mark(11,2,'Physics',0);
Mark(12,2,'Physics',0);

Student(3,"Chris"..)

Mark(13,3,'Mathematics',0);
Mark(14,3,'Mathematics',0);
Mark(15,3,'Mathematics',0);

Mark(16,3,'Physics',0);
Mark(17,3,'Physics',0);
Mark(18,3,'Physics',0);

在这种情况下，我们过滤了约翰的数学分数和阿尔伯特的物理分数，我们根本没有显示克里斯

Answer 1

你可以这样做(结果在这里)

select * 
from student s,mark m
where s.id = m.student_id
and m.mark > 0

Answer 2

要找到所有至少有一个非零分数的学生，我会使用一个存在条件：

select stu.*
from student stu
where exists (select * 
              from mark mrk
              where mrk.student_id = stu.id
                and mrk.mark <> 0);

要找到只有零分的学生要复杂得多，而且需要聚合。

select stu.*
from student stu
where exists (select mrk.student_id
              from mark mrk
              where mrk.student_id = stu.id
              group by mrk.student_id
              having bool_and(mrk.mark = 0));

逻辑上，子查询中不需要group by，因为它只处理单个学生，但是SQL语法规则需要它。

这也可以表示为针对派生表的联接，实际上该表可能会更高效一些：

select stu.*
from student stu
  join (
    select mrk.student_id, bool_and(mrk.mark = 0) as all_marks_are_zero
    from mark mrk
    group by mrk.student_id
  ) m on m.student_id = stu.id and m.all_marks_are_zero

在线示例