Mysql查询动态转换行到列

2021-11-20 00:00:00 pivot sql group-by mysql

MySQL 是否可以将列转换为行，动态添加行所需的列数.我认为我的问题可能与数据透视表有关，但我不确定，除了给出以下示例之外，我不知道如何构建这个问题.

Can MySQL convert columns into rows, dynamically adding as many columns as are needed for the rows. I think my question might be related to pivot tables but I'm unsure and I don't know how to frame this question other than by giving the following example.

给定两个表 A 和 B，它们看起来像

Given a two tables A and B, which look like

表A

+--+-----+----+
|id|order|data|
+--+-----+----+
|1 |1    |P   |
+--+-----+----+
|2 |2    |Q   |
+--+-----+----+
|2 |1    |R   |
+--+-----+----+
|1 |2    |S   |
+--+-----+----+

我喜欢编写如下所示的查询:

I like to write a query that looks like the following:

结果表

+--+-----+-----+
|id|data1|data2|
+--+-----+-----+
|1 |P    |S    |
+--+-----+-----+
|2 |R    |Q    |
+--+-----+-----+

基本上我想把表 B 中的每一行变成结果表中的一列.如果 id=1 的表 B 中添加了一个新条目，那么我希望结果表自动扩展一列以容纳这个额外的数据点.

Basically I want to turn each row in table B into a column in the result table. If there was a new entry was added to table B for id=1, then I want the result table to automatically extend by one column to accommodate this extra data point.

推荐答案

您可以使用 GROUP BY 和 MAX 来模拟枢轴.MySQL 也支持 IF 语句.

You can use GROUP BY and MAX to simulate pivot. MySQL also supports IF statement.

SELECT  ID,
        MAX(IF(`order` = 1, data, NULL)) data1,
        MAX(IF(`order` = 2, data, NULL)) data2
FROM    TableA
GROUP   BY ID

  • SQLFiddle 演示

    • 如果order有多个值，动态SQL可能更合适，这样你就不必修改查询:

    If you have multiple values of order, dynamic SQL may be more appropriate so that you will not have to modify the query:

    SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(`order` = ', `order`, ',data,NULL)) AS data', `order`)
  ) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT  ID, ', @sql, ' 
                  FROM    TableName
                  GROUP   BY ID');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

    • SQLFiddle 演示
    • SQLFiddle 演示(另一个例子)

      • 两个查询的输出:

      ╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║  1 ║ P     ║ S     ║
║  2 ║ R     ║ Q     ║
╚════╩═══════╩═══════╝

相关文章