PHP实现行转动态列：关联双数据集构建矩阵报表

核心需求拆解 #

• 以BLOEMEN表的bloem_id作为首列，第二列展示对应bloem_naam
• 将knollen表中指定年份的teler_id唯一值转为动态列，用plant_aant填充对应单元格
• 需包含行总计（每个花卉的总种植量）和列总计（每个种植者的总种植量）
• 列数随teler_id数量动态变化

实现思路 #

1. 数据库层动态生成转置SQL #

MySQL无原生动态转置功能，需先获取目标年份的所有teler_id，再拼接成聚合列语句，同时关联双表并计算总计：

• 先查询目标年份的teler_id集合，用于拼接动态列
• 用CASE WHEN聚合每个teler_id对应的plant_aant，关联BLOEMEN表获取花卉名称
• 用UNION ALL追加行总计和列总计行

2. PHP层数据处理与渲染 #

通过mysqli执行动态生成的SQL，直接获取结构化结果后渲染成表格，无需复杂数组转换。

示例代码 #

1. PHP动态生成SQL并执行 #

<?php
$conn = mysqli_connect("localhost", "user", "password", "dbname");
$target_year = 2024; // 可根据需求动态传入

// 获取目标年份的所有teler_id
$teler_query = mysqli_query($conn, "SELECT DISTINCT teler_id FROM knollen WHERE jaar = $target_year ORDER BY teler_id");
$teler_ids = [];
while ($row = mysqli_fetch_assoc($teler_query)) {
    $teler_ids[] = $row['teler_id'];
}

// 拼接动态列语句
$pivot_columns = [];
foreach ($teler_ids as $teler) {
    $pivot_columns[] = "SUM(CASE WHEN teler_id = '$teler' THEN plant_aant ELSE 0 END) AS `teler_$teler`";
}
$pivot_sql = implode(", ", $pivot_columns);

// 构建主查询（含行总计）
$main_query = "
SELECT 
    b.bloem_id AS '花ID',
    b.bloem_naam AS '花名称',
    $pivot_sql,
    SUM(k.plant_aant) AS '行总计'
FROM BLOEMEN b
LEFT JOIN knollen k ON b.bloem_id = k.bloem_id AND k.jaar = $target_year
GROUP BY b.bloem_id, b.bloem_naam
UNION ALL
SELECT 
    '总计' AS '花ID',
    '总计' AS '花名称',
    " . implode(", ", array_map(function($teler) use ($target_year) {
        return "SUM(CASE WHEN teler_id = '$teler' THEN plant_aant ELSE 0 END)";
    }, $teler_ids)) . ",
    SUM(plant_aant) AS '行总计'
FROM knollen
WHERE jaar = $target_year
";

// 执行查询
$result = mysqli_query($conn, $main_query);
?>

2. 渲染HTML表格 #

<table border="1">
    <thead>
        <tr>
            <?php
            // 输出表头
            $headers = mysqli_fetch_fields($result);
            foreach ($headers as $header) {
                echo "<th>{$header->name}</th>";
            }
            ?>
        </tr>
    </thead>
    <tbody>
        <?php
        while ($row = mysqli_fetch_assoc($result)) {
            echo "<tr>";
            foreach ($row as $value) {
                echo "<td>" . ($value ?? 0) . "</td>";
            }
            echo "</tr>";
        }
        ?>
    </tbody>
</table>

关键说明 #

• 动态列通过拼接CASE WHEN实现，列数随teler_id数量自动调整
• 使用LEFT JOIN确保无种植记录的花卉也能在报表中显示
• 总计通过UNION ALL追加汇总行，避免前端复杂计算
• 切换年份仅需修改$target_year变量即可

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