MySQL Mysql查询动态转换行到列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14834290/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql query to dynamically convert rows to columns
提问by Dom
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.
MySQL 是否可以将列转换为行，动态添加行所需的尽可能多的列。我认为我的问题可能与数据透视表有关，但我不确定，除了给出以下示例之外，我不知道如何构建这个问题。
Given a two tables A and B, which look like
给定两个表 A 和 B，它们看起来像
Table A
表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:
我喜欢编写如下所示的查询：
Result Table
结果表
+--+-----+-----+
|id|data1|data2|
+--+-----+-----+
|1 |P |S |
+--+-----+-----+
|2 |R |Q |
+--+-----+-----+
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.
基本上我想把表 B 中的每一行变成结果表中的一列。如果 id=1 的表 B 中添加了一个新条目，那么我希望结果表自动扩展一列以容纳这个额外的数据点。
回答by John Woo
You can use GROUP BYand MAXto simulate pivot. MySQL also supports IFstatement.
您可以使用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
If you have multiple values of order, dynamic SQL may be more appropriate so that you will not have to modify the query:
如果您有多个 值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;
OUTPUT OF BOTH QUERIES:
两个查询的输出：
╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║ 1 ║ P ║ S ║
║ 2 ║ R ║ Q ║
╚════╩═══════╩═══════╝
回答by sgeddes
You need to use MAXand GROUP BYto simulate a PIVOT:
您需要使用MAX并GROUP BY模拟 PIVOT：
SELECT Id,
MAX(CASE WHEN Order = 1 THEN data END) data1,
MAX(CASE WHEN Order = 2 THEN data END) data2
FROM TableA
GROUP BY Id
And here is the SQL Fiddle.
这是SQL Fiddle。
