MySQL 数据透视行转换为动态列数

假设我有三个不同的 MySQL 表:

表 products: 

id | name
1   Product A
2   Product B

表 partners: 

id | name
1   Partner A
2   Partner B

表 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

我希望获得一个表，其中行和产品中的合作伙伴作为列。到目前为止，我能得到这样的输出:

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

使用以下查询:

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

但是我想换成这样的:

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

问题是，我不能告诉我将有多少产品，因此需要根据 products 表中的行动态更改列号。

这个非常好的答案似乎不适用于 mysql: 从行值创建表列的可能性

110925 次浏览
小开
最佳答案

Unfortunately MySQL does not have a PIVOT function which is basically what you are trying to do. So you will need to use an aggregate function with a CASE statement:

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

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

It's probably worth noting that GROUP_CONCAT is 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;


提交答案