php - 在左连接上显示 AVG

Question

我想在左连接上显示 StudentQuizReading、StudentQuizLanguage 和 StudentQuizMath 表的 Score 字段的平均值。

我怎样才能做到这一点？

这是迄今为止我的代码:

$query = "
SELECT s.StudentID
     , s.LastName
     , s.FirstName
     , s.MiddleName
     , s.Level
     , s.Adviser
     , r.Score
     , l.Score
     , m.Score 
  FROM Students s
  LEFT 
  JOIN StudentQuizReading r
    ON r.StudentID = s.StudentID
  LEFT 
  JOIN StudentQuizLanguage l
    ON l.StudentID = s.StudentID
  LEFT 
  JOIN StudentQuizMath m
    ON m.StudentID = s.StudentID
 WHERE s.StudentID = '".$_POST["StudentID"]."'
 ";

表的详细信息:StudentQuizReading、StudentQuizLanguage、StudentQuizMath

+-----------+-------------+
| Field     | Type        | 
+-----------+-------------+
| StudentID | int(5)      |
| QuizNum   | varchar(10) | 
| Score     | int(2)      | 
+-----------+-------------+

Answer 1

您可以检查LEFT连接表列中StudentID的null并可以找到平均值。

检查下面的查询。

$query = "SELECT
  Students.StudentID,
  Students.LastName,
  Students.FirstName,
  Students.MiddleName,
  Students.Level,
  Students.Adviser,
  FirstQuarterGrades.Reading,
  FirstQuarterGrades.Language,
  FirstQuarterGrades.Mathematics,
  (SUM(IF(StudentQuizReading.StudentID IS NULL, 0, StudentQuizReading.Score)) / SUM(IF(StudentQuizReading.StudentID IS NULL, 0, 1))) AS StudentQuizReadingAvg,
  (SUM(IF(StudentQuizLanguage.StudentID IS NULL, 0, StudentQuizLanguage.Score)) / SUM(IF(StudentQuizLanguage.StudentID IS NULL, 0, 1))) AS StudentQuizLanguageAvg,
  (SUM(IF(StudentQuizMath.StudentID IS NULL, 0, StudentQuizMath.Score)) / SUM(IF(StudentQuizMath.StudentID IS NULL, 0, 1))) AS StudentQuizMathAvg
FROM Students
  LEFT JOIN StudentQuizReading ON Students.StudentID = StudentQuizReading.StudentID
  LEFT JOIN StudentQuizLanguage ON Students.StudentID = StudentQuizLanguage.StudentID
  LEFT JOIN StudentQuizMath ON Students.StudentID = StudentQuizMath.StudentID
WHERE Students.StudentID = '".$_POST["StudentID"]."'";