MySQL动态交叉表 [英] MySQL dynamic cross tab

问题描述

我有一个这样的表:

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交叉表(数据透视表)的在线解决方案很多，但是如果我不知道有多少种方式"，该怎么办? 谢谢

There are many solutions online about MySQL cross tab (pivot table), but how can I do this if I don't know how many "way" are there? Thanks

推荐答案

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

The number and names of columns must be fixed at the time you prepare the query. That's just the way SQL works.

因此，您有两种选择方式来解决此问题.两种选择都涉及编写应用程序代码:

So you have two choices of how to solve this. Both choices involve writing application code:

(1)查询way的不同值，然后编写代码以使用这些值构造数据透视查询，并在SELECT列表中追加与不同值数量一样多的列.

(1) Query the distinct values of way and then write code to use these to construct the pivot query, appending as many columns in the SELECT-list as the number of distinct values.

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值的数量一样.

Now you can run the new query, and it has as many columns as the number of distinct way values.

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

(2)按数据库中的结构逐行查询数据，然后在显示数据之前编写代码以将数据透视成列.

(2) Query the data row by row as it is structured in your database, and then write code to pivot into columns before you display the data.

$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简单得多.您将查询结果后处理为一组不同的数组.

Now you have an array of arrays that looks the same as if you had run a pivot query, but the actual SQL you ran was a lot simpler. You post-processed the query result into a different set of arrays.

这篇关于MySQL动态交叉表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！