如何将 mysql 表行显示为列 - How do I display mysql table row as column

我有两个表，result和gp。

在result表中，我有这样的东西：

|id||student_id   ||course_code||grade||session  ||level||semester|
|1 ||TR/2213234561||MAT111     ||A    ||2009/2010||100  ||first   |
|2 ||TR/2213234561||MAT112     ||B    ||2009/2010||100  ||first   |
|3 ||TR/2213234561||MAT113     ||C    ||2009/2010||100  ||first   |
|4 ||TR/2213234567||MAT111     ||D    ||2009/2010||200  ||first   |
|5 ||TR/2213234567||MAT112     ||C    ||2009/2010||200  ||first   |
|6 ||TR/2213234567||MAT113     ||C    ||2009/2010||200  ||first   |

然后gp表

|id||student_id   ||session  ||level||semester||gp |
|1 ||TR/2213234561||2009/2010||100  ||first   ||4.2|
|2 ||TR/2213234567||2009/2010||100  ||first   ||3.5|
|3 ||TR/2213234561||2010/2011||200  ||first   ||4.2|
|4 ||TR/2213234567||2010/2011||200  ||first   ||3.5|

我想要的是这样的：

|Matriculation||MAT111||MAT112||MAT113||MAT114||GP |
|TR/2213234561||A     ||B     ||D     ||C     ||4.2|
|TR/2213234567||C     ||D     ||E     ||F     ||3.5|

课程代码不是恒定的 - 这取决于学生注册的课程

我已经这样做了：

<?php
$rst1 = mysql_query("select distinct course_code from result ", $conn);
echo "<table callspacing='4'>";
echo "<tr>";
echo "<td> Matriculation Number </td>";
$c_code = array();
while ($row = mysql_fetch_array($rst1))
{
    $c_code[] = $row['course_code'];
}
foreach($c_code as $c_code)
{
    echo "<td>" .$c_code. "</td>";
}
$sql ="SELECT result.student_id,
       MAX(CASE WHEN course_code = ' $c_code' THEN grade END)  $c_code,
       gp.CTC 
       FROM result 
       JOIN gp  
       ON gp.student_id = result.student_id
       GROUP
       BY student_id";
echo "<td> GP</td>";                         
$rst = mysql_query("$sql",$conn) or die(mysql_error());
while ($row = mysql_fetch_array($rst))
{
    echo "</tr>";
    echo "<tr>";
    echo "<td>" .$row['student_id']. "</td>";
    echo "<td>"  .$row[$c_code]. "</td>";
}
echo "<td>"  .$row[$c_code]. "</td>";
echo "<td>" .$row['CTC']. "</td>";
echo"</tr>";
echo "</table>";
?>

第一个查询是获取课程代码，因为课程不是常量。

有了这段代码，我得到了这样的东西：

|Matriculation||MAT111||MAT112||MAT113||MAT114||GP|
|TR/2213234561|
|TR/2213234567|

但我想要

|Matriculation||MAT111||MAT112||MAT113||MAT114||GP |
|TR/2213234561||A     ||B     ||D     ||C     ||4.2|
|TR/2213234567||C     ||D     ||E     ||F     ||3.5|

任何建议或方向将不胜感激。

您希望做的被称为"透视"您的数据，并且是其他一些RDBMS具有本机支持的东西，但MySQL没有(根据设计，因为开发人员认为此类操作属于表示层(。

但是，您有以下几种选择：

构造一个相当可怕的MySQL查询来手动执行透视操作：

SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP
  FROM gp
  NATURAL JOIN (
    SELECT student_id, grade AS MAT111
    FROM result
    WHERE course_code = 'MAT111'
  ) AS tMAT111
  NATURAL JOIN (
    SELECT student_id, grade AS MAT112
    FROM result
    WHERE course_code = 'MAT112'
  ) AS tMAT112
  -- etc.
WHERE level = @level AND semester = @semester

如果您选择沿着这条路走下去，您可以通过自动生成此查询来使您的生活稍微轻松一些，使用 PHP 中的循环构造或 MySQL 中的预准备语句。

以下是在 PHP 中执行此操作的一种方法：

获取课程列表：

$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
$qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
$courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);

遍历结果，构造上述 SQL：

mb_regex_encoding($charset);
$columns = mb_ereg_replace('`', '``', $courses);
$sql = "
SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
  FROM gp";
foreach ($columns as $column) $sql .= "
  NATURAL JOIN (
    SELECT student_id, grade AS `$column`
    FROM result
    WHERE course_code = ?
  ) AS `t$column`";
$sql .= "
WHERE level = ? AND semester = ?";

执行 SQL，传入课程数组作为参数：

$qry = $dbh->prepare($sql);
$params = $courses;
array_push($params, $level, $semester);
$qry->execute($params);

输出结果：

echo "<table>";
echo "<tr>";
for ($i = 0; $i < $qry->columnCount(); $i++) {
  $meta = $qry->getcolumnMeta($i);
  echo "<th scope='col'>" . htmlentities($meta['name']) . "</th>";
}
echo "</tr>";
while ($row = $qry->fetch(PDO::FETCH_NUM)) {
  echo "<tr>";
  foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
  echo "</tr>";
}
echo "</table>";

执行上述一次性操作，以便更改MySQL数据库的结构以更紧密地反映此所需布局(转换表后很容易，但可能会影响数据库的其他用途(：
```
CREATE TABLE StudentGrades (PRIMARY KEY('Matriculation'))
SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP
  -- etc. as above
```
或者，您可以创建一个VIEW，这是一种基于基础表以这种方式构建的"虚拟表"。
在 PHP 中手动透视数据(相对繁琐(。