本文详解如何在 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 面向对象风格）：

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 '';

    // 表头
    echo '';
    for ($i = 0; $i < $result->field_count; $i++) {
        $field = $result->fetch_field_direct($i);
        echo "";
    }
    echo '';

    // 数据行
    while ($row = $result->fetch_row()) {
        echo '';
        foreach ($row as $cell) {
            echo "";
        }
        echo '';
    }
    echo '
{$field->name}
".($cell === null ? '—' : (int)$cell)."
';
} else {
    echo '
⚠️ 暂无数据或查询失败
';
}

// 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 的聚合与预编译能力，在百万级记录下仍保持毫秒级响应。只要遵循字段校验、索引优化、资源释放三原则，即可在高并发成绩报表系统中稳定运行。

https://www.php.cn/faq/1992701.html