本文详解如何在 mysql 中动态实现“列转行”（即透视表），将科目作为列头、学生成绩横向展开，并通过 php 安全执行预编译语句，兼顾性能与可扩展性。
在教学管理系统或成绩分析场景中，常需将「长表」（每行一条科目成绩）转换为「宽表」（每行一个学生，各科目作为独立列），例如将原始数据：
Stud_id subject Total Abc12 Eng 60 Abc13 Eng 40 Abc12 Math 70 Abc13 Math 50
动态转换为：
Stud_id Eng Math Abc12 60 70 Abc13 40 50
关键在于：科目（subject）不固定——未来可能新增 Bio、Chem 等，因此不能硬编码 CASE WHEN subject='Eng' THEN total END，而需动态生成 SQL。
✅ 正确实现步骤（含防错与优化）
以下为生产环境推荐的完整 PHP + MySQL 实现（基于 MySQLi 面向对象风格）：
<?php
// 假设 $mysqli 已正确初始化并连接数据库
$mysqli = new mysqli($host, $user, $pass, $db);
// 1. 初始化变量（避免 SQL 注入风险：仅用于字段名，仍需白名单校验）
$mysqli->query("SET @sql = NULL");
// 2. 动态构建列名部分（使用 GROUP_CONCAT 拼接所有唯一科目）
$sql_cols = "
SELECT GROUP_CONCAT(DISTINCT
CONCAT(
'IFNULL(SUM(CASE WHEN subject = ''',
`subject`,
''' THEN `Total` END), 0) AS `',
`subject`, '`'
)
) INTO @sql
FROM scores_tbl
WHERE class_name = 'JSS1'; -- 可按需添加筛选条件
";
$mysqli->query($sql_cols);
// 3. 构建完整动态查询（注意：此处 @sql 可能为 NULL，需判空）
$mysqli->query("SELECT @sql AS dynamic_sql"); // 调试用：检查生成的 SQL
$result = $mysqli->query("SELECT @sql");
$row = $result->fetch_row();
if (!$row[0]) {
die("❌ 错误：未查到任何有效科目，请检查 scores_tbl 表数据或 WHERE 条件");
}
// 4. 拼接主查询（包含 stud_id 和动态列）
$mysqli->query("SET @sql = CONCAT('SELECT stud_id, ', @sql, ' FROM scores_tbl WHERE class_name = ''JSS1'' GROUP BY stud_id');");
// 5. 预编译并执行（比直接拼接更安全，且支持缓存执行计划）
$mysqli->query("PREPARE stmt FROM @sql");
$result = $mysqli->query("EXECUTE stmt");
// 6. 渲染 HTML 表格（带错误处理）
if ($result && $result->num_rows > 0) {
echo '<table border="1" class="pivot-table" style="border-collapse:collapse;width:100%">';
// 表头
echo '<tr>';
for ($i = 0; $i < $result->field_count; $i++) {
$field = $result->fetch_field_direct($i);
echo "<th style='background:#f0f0f0;padding:8px;text-align:center;'>{$field->name}</th>";
}
echo '</tr>';
// 数据行
while ($row = $result->fetch_row()) {
echo '<tr>';
foreach ($row as $cell) {
echo "<td style='padding:6px;text-align:right;'>".($cell === null ? '—' : (int)$cell)."</td>";
}
echo '</tr>';
}
echo '</table>';
} else {
echo '<p>⚠️ 暂无数据或查询失败</p>';
}
// 7. 清理资源（重要！防止内存泄漏）
$mysqli->query("DEALLOCATE PREPARE stmt");
$mysqli->close();
?>⚠️ 关键注意事项
- SQL 注入防护：subject 字段虽来自数据库，但仍建议在拼接前做白名单校验（如正则 /^[a-zA-Z0-9_]+$/），禁止含反引号、分号等危险字符；
- 空值处理：使用 IFNULL(..., 0) 确保缺失科目显示为 0，而非 NULL；
-
性能优化：
- 在 scores_tbl(subject, class_name, stud_id) 上建立联合索引：ALTER TABLE scores_tbl ADD INDEX idx_pivot (subject, class_name, stud_id);
- 大数据量时，避免 GROUP_CONCAT 超限（默认 1024 字符），可通过 SET SESSION group_concat_max_len = 10000; 扩容；
- 可维护性增强：将动态 SQL 生成逻辑封装为 MySQL 存储过程，便于复用与权限管控；
- PHP 兼容性：mysqli::multi_query() 不支持 PREPARE/EXECUTE，必须使用单条 query() 分步执行。
✅ 总结
动态行转列本质是「元编程」：先用 SQL 查询出列结构，再拼装并执行新 SQL。相比硬编码或应用层聚合，该方案充分利用 MySQL 的聚合与预编译能力，在百万级记录下仍保持毫秒级响应。只要遵循字段校验、索引优化、资源释放三原则，即可在高并发成绩报表系统中稳定运行。
