php - 如何计算每个学生的成绩总和

Question

我有一个用于考试成绩的表，另一个用于学生的表(学生表始终只有 10 个学生)。

我想输出一个 Json 格式的数组，用于显示学生详细信息，以及数组数组中每个学期的成绩总和。

预期输出

[
    {
    "id": "1", -> this is the semester ID
    "student1": {"id": "1","name": "student name", "bio": "50", "chem": "50", "math": "60", "total grades": "160"},
    "student2": {"id": "2","name": "secondstudent name", "bio": "60", "chem": "60", "math": "50", "total grades": "170"},
    }
]

这是我的表格结构

tb学生

   id   ,    name                
    1   ,    student name        
    2   ,    secondstudent name 
    3   ,    thirdstudent name  

tbGrades

    id   ,  student   ,  semster    ,   bio    ,   chem    ,   math   ,  total
     1   ,     1      ,     1       ,   50     ,    50     ,   60     ,   160
     2   ,     2      ,     1       ,   30     ,    40     ,   20     ,   90
     3   ,     2      ,     1       ,   30     ,    20     ,   30     ,   80

挑战

如何按学期计算每个学生生物、化学、数学、总计的总和，因为学生可能在同一学期有多个成绩

我尝试过的

$sql = "SELECT grade.id g_id, grade.semester g_semester, grade.bio g_bio, grade.chem g_chem, grade.math g_math, grade.total g_total, student.id s_id, student.name s_name FROM tbGrades AS grade INNER JOIN tbStudents AS student ON student.id = grade.student";

try {
    $db = new db();
    $db = $db->connect();
    $stmt = $db->prepare($sql);

    $stmt->execute();

    $grade = $stmt->fetchAll(PDO::FETCH_OBJ);

    $db = null;

    if(empty($grade)) {
        $response->getBody()->write
        ('
        {
            "error":
            {
                "status":"400",
                "message":"Invalid Request"
            }
        }');
    } else {
         foreach($grade as $value) {
            $array_resp[]=[
                'id' => $value->g_id,
                'student1' => ['id'=>$value->s_id, 'name'=>$value->s_name, 'bio'=>$value->g_bio, 'chem'=>$value->g_chem, 'math'=>$value->g_math, 'total grades'=>$value->g_total],
             ];
        }
        $response->getBody()->write(json_encode($array_resp));
    }
} catch(PDOException $e) {
    $response->getBody()->write
    ('
    {
        "error":
        {
            "message":'. $e->getMessage() .'
        }
    }');
}

Answer 1

您可以使用 MySQL 轻松完成此操作 GROUP BY条款。

SELECT 
grade.id g_id, 
grade.semester g_semester, 
SUM(grade.bio) g_bio, 
SUM(grade.chem) g_chem, 
SUM(grade.math) g_math, 
SUM(grade.total) g_total, 
student.id s_id, 
student.name s_name
FROM 
tbGrades AS grade 
INNER JOIN 
tbStudents AS student 
ON 
student.id = grade.student
GROUP BY
student.id, 
grade.semester