sql - PostgreSQL 或 Rails 中的部分透视

Question

假设我有 3 个表，我不能更改任何 DDL

Class
id: integer
name: string

和

Student
id: integer
name: string
class_id: integer //Foreign key to Class table

和

Score
id: integer
student_id: integer //Foreign key to Student table
subject: string //String enum of "Math", "Physics", "Chemistry"
score: float

另外，假设字符串枚举总是正确的，只填充这三个可能值中的一个。

我需要这样的结果查询
学生编号 |学生姓名 |类名 |数学 |物理学 |化学
其中math field是id为student_id的学生“Math”科目的平均分，physics field是“Physics”科目的平均分数，chemistry field是“Chemistry”科目的平均分数。

我知道我可以在 Rails ActiveRecord::Base 中处理它，但即使使用 include，我最终也会使用很多查询。

如何仅使用一个查询生成所需的输出？

Answer 1

您可以通过连接和聚合来做到这一点:

select s.student_id, s.student_name, c.class_name,
       avg(case when sc.subject = 'Math' then sc.score end) as Math,
       avg(case when sc.subject = 'Physics' then sc.score end) as Physics,
       avg(case when sc.subject = 'Chemistry' then sc.score end) as Chemistry
from students s join
     scores sc 
     on sc.student_id = s.id join
     classes c
     on sc.class_id = cl.id
group by s.student_id, s.student_name, c.class_name;