MySQL查询将行动态转换为列

Z时代
2024-01-10
分类：问答

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

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

表A

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

我想编写一个如下查询：

结果表

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

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

回答：

您可以使用GROUP BY和MAX模拟枢轴。MySQL还支持IF语句。

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

SQLFiddle演示

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

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;