SQL 查询以查找在某个科目注册的学生总数

Question

类表有 class_id 和 class_name 列，其值为 ff:

class_id | class_Name
1        | Algebra
2        | History
3        | PE

学生表有 student_id 和 student_name 列:

student_id | student_name
1          | Kylo Ren
2          | Rey

第三个表是两个表的联结表，具有以下列 class_id 和 student_id

class_id | student_id
1        | 1
1        | 2
2        | 1
3        | 2

如何找到每个科目的注册学生人数？

输出应该是这样的

Class Name           | Number of Students
Algebra              | 2
History              | 1
PE                   | 1

然后编辑查询，让它只显示类(class)名称和大于 2 的学生人数

谢谢!

Answer 1

按学生聚合联结表，然后断言匹配的学生拥有所有类(class):

WITH cte AS (
    SELECT student_id
    FROM junction
    GROUP BY student_id
    HAVING COUNT(class_id) = (SELECT COUNT(*) FROM class)
)

SELECF COUNT(*) AS num_all_classes FROM cte;