如何在MySQL与PHP中将学生多门课程成绩合并为单条记录

我有三张数据表：courses（课程表）、students（学生表）、results（成绩表），课程表有约9门课程。学生考试成绩存入results表，示例数据如下：

id student_id course_id score
1  12345      23456     50
2  12345      12345     20
...

已知课程ID对应关系：23455 → MS Word，12345 → PowerPoint。

我需要把每位学生的所有成绩合并到单行记录，比如：

1. elexis promise 123322 50 MS Word 20 PowerPoint

但当前SQL查询返回的是每门课程一行：

SELECT s.first_name, s.last_name, s.ap_number, r.student_id, r.course_id, r.total_score, c.course_id, c.course_title
FROM students AS s
JOIN result AS r
    ON s.ap_number = r.student_id
JOIN courses AS c
    ON r.course_id = c.course_id

查询结果示例：

1. elexis promise 123322 50 MS Word
2. elexis promise 123322 20 PowerPoint
...

同时HTML展示代码也有问题，循环输出时每一行都重复学生信息，且成绩列都显示同一个值：

<thead>
    <tr>
        <th class="ml-5">ID</th>
        <th>Name</th>
        <th>Power Point</th>
        <th>Word</th>
        <th>----</th>
    </tr>
</thead>
<tbody>
<?php $id = 0;
foreach ($results as  $value) {?>
<tr>
    <td><?php echo $id += 1 ?></td>
    <td><?php echo "{$value["first_name"]} {$value["last_name"]}" ?></td>
    <td><?php echo $value["total_score"] ?></td>
    <td><?php echo $value["total_score"] ?></td>
    <td><?php echo ---- ?></td>
</tr>
<?php }
?>

1. 调整SQL查询（行转列） #

用条件聚合把多行成绩转成单行的多列，因为每个学生每门课只有一个成绩，用MAX()或SUM()都可以。示例如下（以PowerPoint和MS Word为例，其他课程可以按同样方式扩展）：

SELECT 
    s.first_name,
    s.last_name,
    s.ap_number,
    -- 获取PowerPoint成绩
    MAX(CASE WHEN c.course_id = 12345 THEN r.total_score END) AS powerpoint_score,
    -- 获取MS Word成绩
    MAX(CASE WHEN c.course_id = 23455 THEN r.total_score END) AS word_score,
    -- 其他课程依次添加：MAX(CASE WHEN c.course_id = 课程ID THEN r.total_score END) AS 课程名_score
    -- 比如：MAX(CASE WHEN c.course_id = 23456 THEN r.total_score END) AS excel_score
FROM students AS s
LEFT JOIN result AS r ON s.ap_number = r.student_id
LEFT JOIN courses AS c ON r.course_id = c.course_id
GROUP BY s.first_name, s.last_name, s.ap_number;

注意：用LEFT JOIN替代JOIN，避免漏掉没有成绩的学生；GROUP BY要包含所有非聚合的学生字段。

2. 调整HTML/PHP展示代码 #

现在SQL返回的是每个学生一行，直接循环输出每一行即可，对应列赋值：

<thead>
    <tr>
        <th class="ml-5">ID</th>
        <th>Name</th>
        <th>Power Point</th>
        <th>Word</th>
        <!-- 添加其他课程的表头 -->
        <th>----</th>
    </tr>
</thead>
<tbody>
<?php $id = 0;
foreach ($results as  $value) {?>
<tr>
    <td><?php echo ++$id; ?></td>
    <td><?php echo "{$value['first_name']} {$value['last_name']}"; ?></td>
    <td><?php echo $value['powerpoint_score'] ?? '-'; ?></td> <!-- 没有成绩显示'-' -->
    <td><?php echo $value['word_score'] ?? '-'; ?></td>
    <!-- 其他课程成绩列依次添加：<td><?php echo $value['excel_score'] ?? '-'; ?></td> -->
    <td>----</td>
</tr>
<?php } ?>
</tbody>

用?? '-'处理没有成绩的情况，避免显示空值；表头要和SQL查询的列名对应。

内容的提问来源于stack exchange，提问作者Elex Promise