MySQL Mysql查询基于两列动态转换行到列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17964078/
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 on the basis of two columns
提问by Moon
I have followed a questionhere to use a Mysql query to dynamically convert rows to columns. This works fine, but i need to convert this on basis of two columns,
我在这里关注了一个问题，以使用 Mysql 查询将行动态转换为列。这工作正常，但我需要在两列的基础上转换它，
The query mentioned in above link works for a single column "data", but i want to work for two columns which are "data" and "price".
上面链接中提到的查询适用于单列“数据”，但我想适用于“数据”和“价格”两列。
i have added an example here,
我在这里添加了一个例子，
Given a table A, which look like
给定一个表 A，它看起来像
Table A
| id|order|data|item|Price|
-----+-----+----------------
| 1| 1| P| 1 | 50 |
| 1| 1| P| 2 | 60 |
| 1| 1| P| 3 | 70 |
| 1| 2| Q| 1 | 50 |
| 1| 2| Q| 2 | 60 |
| 1| 2| Q| 3 | 70 |
| 2| 1| P| 1 | 50 |
| 2| 1| P| 2 | 60 |
| 2| 1| P| 4 | 80 |
| 2| 3| S| 1 | 50 |
| 2| 3| S| 2 | 60 |
| 2| 3| S| 4 | 80 |
I like to write a query that looks like the following:
我喜欢编写如下所示的查询：
Result Table
| id|order1|order2|order3|item1|item2|item3|item4|
-----+-----+---------------------------------------
| 1| P | Q | | 50 | 60 | 70 | |
| 2| P | | S | 50 | 60 | | 80 |
I have tried to create two different queries and then a join to achieve this, but that may not be a good solution. Can any one suggest a solution same like mentioned in the link above.
我尝试创建两个不同的查询，然后创建一个连接来实现这一点，但这可能不是一个好的解决方案。任何人都可以提出与上面链接中提到的相同的解决方案。
Thanks
谢谢
回答by Taryn
If you had a known number of values for both orderand item, then you could hard code the query into:
如果您有已知数量的orderand值item，那么您可以将查询硬编码为：
select id,
max(case when `order` = 1 then data end) order1,
max(case when `order` = 2 then data end) order2,
max(case when `order` = 3 then data end) order3,
max(case when item = 1 then price end) item1,
max(case when item = 2 then price end) item2,
max(case when item = 3 then price end) item3,
max(case when item = 4 then price end) item4
from tableA
group by id;
See Demo. But part of the problem that you are going to have is because you are trying to transform multiple columns of data. My suggestion to get the final result would be to unpivot the data first. MySQL does not have an unpivot function but you can use a UNION ALL to convert the multiple pairs of columns into rows. The code to unpivot will be similar to the following:
见演示。但是您将遇到的部分问题是因为您正在尝试转换多列数据。我对获得最终结果的建议是首先对数据进行逆透视。MySQL 没有 unpivot 功能，但您可以使用 UNION ALL 将多对列转换为行。unpivot 的代码将类似于以下内容：
select id, concat('order', `order`) col, data value
from tableA
union all
select id, concat('item', item) col, price value
from tableA;
See Demo. The result of this will be:
见演示。这样做的结果将是：
| ID | COL | VALUE |
-----------------------
| 1 | order1 | P |
| 1 | order1 | P |
| 1 | order1 | P |
| 1 | item1 | 50 |
| 1 | item2 | 60 |
| 1 | item3 | 70 |
As you can see this has taken the multiple columns of order/dataand item/priceand convert it into multiple rows. Once that is completed, then you can convert the values back into columns using an aggregate function with a CASE:
正如您所看到的，这采用了order/data和item/的多列price并将其转换为多行。完成后，您可以使用带有 CASE 的聚合函数将值转换回列：
select id,
max(case when col = 'order1' then value end) order1,
max(case when col = 'order2' then value end) order2,
max(case when col = 'order3' then value end) order3,
max(case when col = 'item1' then value end) item1,
max(case when col = 'item2' then value end) item2,
max(case when col = 'item3' then value end) item3
from
(
select id, concat('order', `order`) col, data value
from tableA
union all
select id, concat('item', item) col, price value
from tableA
) d
group by id;
See Demo. Finally, you need to convert the above code into a dynamic prepared statement query:
见演示。最后，需要将上面的代码转换成动态的预处理语句查询：
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when col = ''',
col,
''' then value end) as `',
col, '`')
) INTO @sql
FROM
(
select concat('order', `order`) col
from tableA
union all
select concat('item', `item`) col
from tableA
)d;
SET @sql = CONCAT('SELECT id, ', @sql, '
from
(
select id, concat(''order'', `order`) col, data value
from tableA
union all
select id, concat(''item'', item) col, price value
from tableA
) d
group by id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with demo. This gives a result:
请参阅带有演示的 SQL Fiddle。这给出了一个结果：
| ID | ORDER1 | ORDER2 | ORDER3 | ITEM1 | ITEM2 | ITEM3 | ITEM4 |
-------------------------------------------------------------------
| 1 | P | Q | (null) | 50 | 60 | 70 | (null) |
| 2 | P | (null) | S | 50 | 60 | (null) | 80 |
