MySQL动态交叉表

Question

MySQL动态交叉表

mysqlaggregate-functionspivot-tablecrosstab

4

我有一个表格，像这样：

way     stop    time
1       1       00:55
1       2       01:01
1       3       01:07
2       2       01:41
2       3       01:47
2       5       01:49
3       1       04:00
3       2       04:06
3       3       04:12

我希望有一个像这样的表格：

stop    way_1   way_2   way_3   (way_n)
1       00:55           04:00
2       01:01   01:41   04:06
3       01:07   01:47   04:12
5               01:49

关于MySQL交叉表（数据透视表），网上有很多解决方案，但如果我不知道有多少“方式”，该怎么办？

- Paolo Cesari

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

在准备查询时，列的数量和名称必须固定。这就是SQL工作的方式。

所以你有两种解决方案。两种选择都需要编写应用程序代码：

(1) 查询 way 的不同值，然后编写代码使用它们来构建透视查询，将SELECT列表中的列数添加为不同值的数量。

foreach ($pdo->query("SELECT DISTINCT `way` FROM `MyTable`") as $row) {
  $way = (int) $row["way"];
  $way_array[] = "MAX(IF(`way`=$way, `time`)) AS way_$way";
}
$pivotsql = "SELECT stop, " . join(", ", $way_array) .
   "FROM `MyTable` GROUP BY `stop`";

现在，您可以运行新查询，它将具有与不同 way 值数量相同的列。

$pivotstmt = $pdo->query($pivotsql);

(2) 逐行查询数据，因为它是按照你的数据库结构进行组织的，然后编写代码将其旋转成列，然后再显示数据。

$stoparray = array();
foreach ($pdo->query("SELECT * FROM `MyTable`") as $row) {
  $stopkey = $row["stop"];
  if (!array_key_exists($stopkey, $stoparray)) {
    $stoparray[$stopkey] = array("stop"=>$stopkey);
  }
  $waykey = "way_" . $row["way"];
  $stoparray[$stopkey][$waykey] = $row["time"];
}

现在你有一个二维数组，它看起来就像运行了一个透视查询一样，但实际上你运行的 SQL 查询要简单得多。你对查询结果进行了后处理，得到了不同的一组数组。