PostgreSQL-在string_agg上加入 [英] PostgreSQL - JOIN on string_agg

问题描述

标题可能会引起误解，因为我似乎找不到合适的名称.但是就这样.

The title may be misleading because I just can't seem to find the right for it. But here it goes.

我有三个桌子.

Students
student_id | name
1            Rhon

Subjects
subject_id | subject_name | student_id
1            Physics        1
2            Math           1

Grades
grade_id | student_id | subject_id | grade
1          1            1            90
2          1            2            89
3          1            2            88

我希望得到这样的结果:

I would like to result to be like this:

student_id | student_name | subject_name | grades
1            Rhon           Physics        90
1            Rhon           Math           88,89

我当前的查询是:

SELECT students.student_id, subjects.subject_id, string_agg(grades.grade, ',')
FROM students
JOIN subjects ON students.student_id = subjects.student_id
JOIN grades ON subjects.subject_id = grades.subject_id;

我的查询有问题吗?我想念什么吗?该错误表明student_id必须位于GROUP BY子句中，但我不希望这样.谢谢你.

Is there something wrong with my query? Am I missing something? The error says that student_id needs to be in a GROUP BY clause but I don't want that. Thanks in advanced.

推荐答案

您可以使用子查询来做到这一点:

You can do this with a sub-query:

SELECT s.student_id, s.student_name, j.subject_name, g.grades
FROM students s
JOIN subjects j
JOIN (
  SELECT student_id, subject_id, string_agg(grade, ',') AS grades
  FROM grades
  GROUP BY student_id, subject_id) g USING (student_id, subject_id);

您为什么不想GROUP BY student_id?

这篇关于PostgreSQL-在string_agg上加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！