Postgres - SQL 查询从多对多映射中提取类似交叉表的功能

Question

我在 PostgreSQL 数据库中有一个由 Student 和 Subject 组成的数据集。关系类似于：

学生：
身份证号，
姓名，
...

Student ID  | Name      | ... 

1           | Ramesh    | ... 

2           | Suresh    | ...

主题：
身份证号，
姓名，
...

Subject ID  | Name      | ...

1           | Maths     | ...

2           | Science   | ...

Student_Subject：
身份证号，
学生 ID，
subject_id，
...

第三个表，正如人们想象的那样，它是一种表示多对多映射的形式。假设一个学生选择了 3 个科目，那么在 Student_subject 表中他将有 3 个针对其 ID 的映射。

学生可以选择的科目数量没有限制，可以是 0 到 10 之间的任何值。

我需要创建一个 SQL 查询，它将获取以下格式的记录：

Student ID  | Student Name  | Maths         | Science       | ...   | History
1           | Ramesh        | Y             | N             | ...   | Y
2           | Suresh        | N             | Y             | ...   | Y

科目名称可以硬编码为列别名，即美好的。

有人可以建议如何实现这一点吗？

我尝试使用 case when 技术：

    select 
        stud.name,
        (case when sub.name = 'Maths' then 'Y' else 'N' end) "Maths",
        (case when sub.name = 'Science' then 'Y' else 'N' end) "Science", 
        ...
    from student stud
        inner join student_subject s_s on s_s.student_id = stud.id
        inner join subject sub on sub.id = s_s.student_id
    ;

但这样我就不会为每个学生获取一行。如果学生选择了 3 个科目，我会得到 3 个不同的行，每行的每个科目都有一个 Y 值。

Answer 1

基于固定科目列表的静态解决方案：

您必须按学生对结果行进行分组，以便为​​每个学生获取一行，并且对于每个组，使用返回 True 的 bool_or() 聚合函数当组中至少一行返回 True 时：

select 
    stud.name,
    bool_or(case when sub.name = 'Maths' then True else False end) "Maths",
    bool_or(case when sub.name = 'Science' then True else False end) "Science", 
    ...
from student stud
    inner join student_subject s_s on s_s.student_id = stud.id
    inner join subject sub on sub.id = s_s.student_id
group by stud.name

当主题列表可能演变时动态解决方案：

请参阅 如何动态添加列PostgresSQL 选择？