如何将mysql表行显示为列 [英] How do I display mysql table row as column
问题描述
我有两个表result和gp.
在result表中，我有类似以下内容:
In the result table I have something like this:
|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|
课程代码不是恒定的-取决于学生注册的课程
The course code are not constant - it depends on the course registered by the students
我已经这样做了:
<?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>";
?>
第一个查询是获取课程代码，因为课程不是常数.
The first query was to get the course code, since the courses are not constants.
使用该代码，我得到了这样的东西:
with that code, I got something like this:
|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|
任何建议或方向将不胜感激.
Any suggestion or direction will be highly appreciated.
推荐答案
您想要做的事情被称为透视"数据，并且其他RDBMS对此提供了本机支持，但是MySQL却没有(根据设计，因为开发人员认为这种操作属于表示层.
What you wish to do is known as "pivoting" your data and is something for which some other RDBMS have native support, but MySQL does not (by design, as the developers feel that such manipulations belong in the presentation layer).
但是，您有一些选择:
-
构造一个相当可怕的MySQL查询以手动执行数据透视操作:
Construct a rather horrible MySQL query to perform the pivoting operation manually:
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中的预备语句自动生成此查询，从而使您的生活稍微容易些.
If you choose to go down this path, you can make your life slightly easier by generating this query automatically, using either a looping construct in PHP or a prepared statement in MySQL.
这是您可以在PHP中完成此操作的一种方法:
Here is one way that you could do that in PHP:
-
获取课程列表:
Obtain a list of courses:
$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:
Loop over the results, constructing the above 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，将课程数组作为参数传递:
Execute the SQL, passing in the array of courses as parameters:
$qry = $dbh->prepare($sql);
$params = $courses;
array_push($params, $level, $semester);
$qry->execute($params);
输出结果:
Output the results:
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数据库的结构，以更紧密地反映此所需的布局(转换表后很容易，但可能会影响数据库的其他用途):
Do the above as a one-off operation so that the structure of your MySQL database is changed to more closely reflect this desired layout (easy once table is converted, but may impact other uses of the database):
CREATE TABLE StudentGrades (PRIMARY KEY('Matriculation'))
SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP
-- etc. as above
或者，您可以创建 VIEW 是一种基于基础表以这种方式构造的虚拟表".
Alternatively, you can create a VIEW which is a sort of "virtual table" structured in this way based on the underlying table.
在PHP中手动旋转数据(相对乏味).
Pivot the data manually in PHP (relatively tedious).
这篇关于如何将mysql表行显示为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
