php - 如何在html和php中制作PIVOT(Crosstab)

Question

如果有人能解决这个问题，那对我来说将是一个很大的帮助。我有一个 MySql 数据库表，其中包含以下记录:

student_id    subject_id    scores
s01           eng01         10.00
s01           math01        10.00
s01           science01     10.00
s01           physic01      10.00
s02           eng01         20.00
s02           math01        20.00
s02           science01     20.00
s02           physic01      20.00

我想以这样的方式在 html 和 php 中显示

student    eng01    math01    science01    physic01
s01        10       10        10           10
s02        20       20        20           20

我已经完成了不同组的 sql 查询以及 php 代码，但我仍然无法完成它。非常感谢您的帮助

Answer 1

如果您事先知道所有主题名称，则可以使用 CASE 表达式创建数据透视表:

SELECT student_id,
    MAX(CASE WHEN subject_id = 'eng01' THEN scores ELSE 0 END) AS eng01,
    MAX(CASE WHEN subject_id = 'math01' THEN scores ELSE 0 END) AS math01,
    MAX(CASE WHEN subject_id = 'science01' THEN scores ELSE 0 END) AS science01,
    MAX(CASE WHEN subject_id = 'physic01' THEN scores ELSE 0 END) AS physic01
FROM students
GROUP BY student_id

输出(用于您的示例数据)

student_id  eng01   math01  science01   physic01
s01         10      10      10          10
s02         20      20      20          20

如果您不知道所有主题名称，您可以使用此查询来获取列表:

SELECT DISTINCT subject_id FROM students

然后在 PHP 中，您可以创建一个循环来根据此循环的结果构建查询，类似于(假设您在名为 $result 的变量中拥有该查询的结果)这。请注意，此代码还保存主题名称以供以后用于输出表的标题行。

$sql = 'SELECT student_id';
$headings = array();
while ($row = $result->fetch_array()) {
   $sql .= ", MAX(CASE WHEN subject_id = '{$row['subject_id']}' THEN scores ELSE 0 END) AS {$row['subject_id']}";
   $headings[] = $row['subject_id'];
}
$sql .= ' FROM students GROUP BY student_id';
// run query

要将这些数据输出到 HTML 表中，您可以使用类似这样的代码。我再次假设查询的输出位于 $result 中:

echo "<table>\n<thead><tr><th>Student ID</th><th>" . implode('</th><th>', $headings) . "</th></tr></thead>\n<tbody>";
while ($row = $result->fetch_array()) {
    echo "<tr><td>{$row['student_id']}</td>";
    foreach ($headings as $h) {
        echo "<td>{$row[$h]}</td>";
    }
    echo "</tr>\n";
}
echo "</tbody></table>\n";