MySQL 将行转换为动态列数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12004603/
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 pivot row into dynamic number of columns
提问by FeeJai
Lets say I have three different MySQL tables:
假设我有三个不同的 MySQL 表：
Table products:
表products：
id | name
1 Product A
2 Product B
Table partners:
表partners：
id | name
1 Partner A
2 Partner B
Table sales:
表sales：
partners_id | products_id
1 2
2 5
1 5
1 3
1 4
1 5
2 2
2 4
2 3
1 1
I would like to get a table with partners in the rows and products as columns. So far I was able to get an output like this:
我想得到一个表，行中的合作伙伴和产品为列。到目前为止，我能够得到这样的输出：
name | name | COUNT( * )
Partner A Product A 1
Partner A Product B 1
Partner A Product C 1
Partner A Product D 1
Partner A Product E 2
Partner B Product B 1
Partner B Product C 1
Partner B Product D 1
Partner B Product E 1
Using this query:
使用此查询：
SELECT partners.name, products.name, COUNT( * )
FROM sales
JOIN products ON sales.products_id = products.id
JOIN partners ON sales.partners_id = partners.id
GROUP BY sales.partners_id, sales.products_id
LIMIT 0 , 30
but I would like to have instead something like:
但我想改为：
partner_name | Product A | Product B | Product C | Product D | Product E
Partner A 1 1 1 1 2
Partner B 0 1 1 1 1
The problem is that I cannot tell how many products I will have so the column number needs to change dynamically depending on the rows in the products table.
问题是我无法确定我将拥有多少产品，因此列号需要根据产品表中的行动态更改。
This very good answer does not seem to work with mysql: T-SQL Pivot? Possibility of creating table columns from row values
这个很好的答案似乎不适用于 mysql：T-SQL Pivot? 从行值创建表列的可能性
回答by Taryn
Unfortunately MySQL does not have a PIVOTfunction which is basically what you are trying to do. So you will need to use an aggregate function with a CASEstatement:
不幸的是，MySQL 没有一个PIVOT基本上是你想要做的功能。因此，您需要使用带有CASE语句的聚合函数：
select pt.partner_name,
count(case when pd.product_name = 'Product A' THEN 1 END) ProductA,
count(case when pd.product_name = 'Product B' THEN 1 END) ProductB,
count(case when pd.product_name = 'Product C' THEN 1 END) ProductC,
count(case when pd.product_name = 'Product D' THEN 1 END) ProductD,
count(case when pd.product_name = 'Product E' THEN 1 END) ProductE
from partners pt
left join sales s
on pt.part_id = s.partner_id
left join products pd
on s.product_id = pd.prod_id
group by pt.partner_name
See SQL Demo
参见SQL 演示
Since you do not know the Products you will probably want to perform this dynamically. This can be done using prepared statements.
由于您不知道产品，您可能希望动态执行此操作。这可以使用准备好的语句来完成。
With dynamic pivot tables (transform rows to columns) your code would look like this:
使用动态数据透视表（将行转换为列），您的代码如下所示：
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'count(case when Product_Name = ''',
Product_Name,
''' then 1 end) AS ',
replace(Product_Name, ' ', '')
)
) INTO @sql
from products;
SET @sql = CONCAT('SELECT pt.partner_name, ', @sql, ' from partners pt
left join sales s
on pt.part_id = s.partner_id
left join products pd
on s.product_id = pd.prod_id
group by pt.partner_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Demo
参见SQL 演示
It's probably worth noting that GROUP_CONCATis by default limited to 1024 bytes. You can work around this by setting it higher for the duration of your procedure, ie. SET @@group_concat_max_len = 32000;
可能值得注意的GROUP_CONCAT是，默认情况下限制为 1024 字节。您可以通过在您的程序期间将其设置得更高来解决此问题，即。SET @@group_concat_max_len = 32000;
