MySQL动态交叉表

2021-11-20 00:00:00 pivot-table mysql aggregate-functions crosstab

我有一张这样的桌子:

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

我想要一张这样的桌子:

and I want a table like this:

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($pivotsql);

(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.

相关文章