mysql - SQL选择学生的平均成绩

Question

我正在学习 sql，但在这方面遇到了一些麻烦，我需要每个学生在每个科目上的平均成绩。

我制作了以下表格。

students
|id_student|name|

subjects
|id_subject|name|

grades
|id_grade|value|

我使用这些表链接它们:

students_subjects
|id_student|id_subject|

subjects_grades
|id_subject|id_grade|

students_grades
|id_student|id_grade|

感谢任何帮助

我在努力

SELECT students.name, subjects.name, grades.value
FROM students
INNER JOIN students_subjects
ON students.id_student = students_subjects.id_student
INNER JOIN subjects
ON subjects.id_subject = students_subjects.id_subject
INNER JOIN students_grades
ON students_grades.id_student = students.id_student
INNER JOIN grades
ON students_grades.id_grade = grades.id_grade
INNER JOIN subjects_grades
ON grades.id_grade = subjects_grades.id_grade

我得到下表

|     name |    name | value |
|----------|---------|-------|
|     Nico |  class1 |    70 |
|     Nico |  class1 |    40 |
|     Nico |  class2 |    70 |
|     Nico |  class2 |    40 |
|    Fadia |  class1 |    60 |
|    Fadia |  class1 |    55 |
| Cristian |  class2 |    50 |
| Cristian |  class2 |    40 |

但是如果我做 AVG(grades.value) 我只会得到第一行

Answer 1

只需按以下方式添加一个组:

SELECT students.name, subjects.name, AVG(grades.value)
FROM students
    INNER JOIN students_subjects
    ON students.id_student = students_subjects.id_student
    INNER JOIN subjects
    ON subjects.id_subject = students_subjects.id_subject
    INNER JOIN students_grades
    ON students_grades.id_student = students.id_student
    INNER JOIN grades
    ON students_grades.id_grade = grades.id_grade
    INNER JOIN subjects_grades
    ON grades.id_grade = subjects_grades.id_grade
GROUP BY students.name, subjects.name