如何在MySQL中动态行转列实现学生成绩表排版

要实现每行对应一名学生的成绩单排版，需从数据查询和前端渲染两部分调整：

1. 优化SQL查询（按学生分组并聚合成绩） #

原查询会返回单条成绩记录，我们通过分组+条件聚合，把每个学生的各科成绩、总分、平均分一次性查询出来：

$query = mysqli_query($conn, "
    SELECT 
        s.stu_id,
        s.name,
        MAX(CASE WHEN sub.subject_title = '数学' THEN sc.exam ELSE 0 END) AS math_score,
        MAX(CASE WHEN sub.subject_title = '英语' THEN sc.exam ELSE 0 END) AS english_score,
        SUM(sc.exam) AS total_score,
        ROUND(AVG(sc.exam), 2) AS average_score
    FROM student s
    JOIN score sc ON s.stu_id = sc.scostu_id
    JOIN subject sub ON sc.scosbj_id = sub.subject_id
    GROUP BY s.stu_id, s.name
");

关键逻辑： #

• CASE WHEN将纵向的科目成绩映射为横向列（数学、英语），无成绩时显示0
• GROUP BY s.stu_id, s.name确保每个学生仅返回一行数据
• SUM计算总分，AVG+ROUND计算并保留2位小数的平均分

2. 修改前端表格渲染 #

调整表头和循环逻辑，直接输出聚合后的学生数据：

<table id="myTable" class="table table-bordered table-striped">
    <thead>
        <th>学号</th>
        <th>姓名</th>
        <th>数学</th>
        <th>英语</th>
        <th>总分</th>
        <th>平均分</th>
    </thead>
    <tbody>
    <?php
        include('db/conn.php');

        $query = mysqli_query($conn, "
            SELECT 
                s.stu_id,
                s.name,
                MAX(CASE WHEN sub.subject_title = '数学' THEN sc.exam ELSE 0 END) AS math_score,
                MAX(CASE WHEN sub.subject_title = '英语' THEN sc.exam ELSE 0 END) AS english_score,
                SUM(sc.exam) AS total_score,
                ROUND(AVG(sc.exam), 2) AS average_score
            FROM student s
            JOIN score sc ON s.stu_id = sc.scostu_id
            JOIN subject sub ON sc.scosbj_id = sub.subject_id
            GROUP BY s.stu_id, s.name
        ");
        
        while($row = mysqli_fetch_array($query)){
    ?>
        <tr>
            <td align="center"><?php echo $row['stu_id']; ?></td>
            <td><span style="text-transform:uppercase;"><?php echo $row['name']; ?></span></td>
            <td><?php echo $row['math_score']; ?></td>
            <td><?php echo $row['english_score']; ?></td>
            <td><?php echo $row['total_score']; ?></td>
            <td><?php echo $row['average_score']; ?></td>
        </tr>
    <?php
        }
    ?>
    </tbody>
</table>

补充说明： #

• 若需新增科目，只需在SQL中添加对应MAX(CASE WHEN ...)语句即可
• 若要显示无成绩的学生，可将JOIN改为LEFT JOIN，确保所有学生都能出现在列表中

内容的提问来源于stack exchange，提问作者Daniel Agboje