我有三张桌子。
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
我希望结果是这样的:
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 子句中,但我不希望这样。
您可以使用子查询来完成此操作:
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
?
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)