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来模拟pivot.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 ║
╚════╩═══════╩═══════╝
相关文章