尝试从一个表获取 AVG，同时联接多个表

Question

SELECT  students.student_name, courses.course_name, grades.grade
FROM students, student_enrollment, grades, courses
WHERE students.id=student_enrollment.student_id 
AND student_enrollment.id=grades.student_enrollmet_id 
AND student_enrollment.course_id=courses.id

使用 MS Access 我正在尝试获取每个科目的学生姓名和平均成绩(课程名称(。当我尝试添加"选择AVG(students.student_name，courses.course_name，grades.grade("时，它不起作用。

Answer 1

SELECT 
s.student_name, c.course_name, avgGrades.resultGrade
FROM 
((SELECT 
se.course_id, se.student_id, AVG(g.grade) AS resultGrade
FROM
grades g
INNER JOIN student_enrollment se ON g.student_enrollment_id = se.id
GROUP BY se.course_id, se.student_id) avgGrades
INNER JOIN students s ON avgGrades.student_id = s.id)
INNER JOIN courses c ON avgGrades.course_id = c.id