MySQL动态枢轴 [英] MySQL dynamic-pivot
本文介绍了MySQL动态枢轴的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张这样的产品零件表:
I have a table of product parts like this:
零件
part_id part_type product_id
--------------------------------------
1 A 1
2 B 1
3 A 2
4 B 2
5 A 3
6 B 3
并且我想要一个查询,该查询将返回如下表:
and I want a query that will return a table like this:
product_id part_A_id part_B_id
----------------------------------------
1 1 2
2 3 4
3 5 6
在实际实施中,将有数百万个产品零件
In its actual implementation there will be millions of product parts
推荐答案
不幸的是,MySQL没有PIVOT
函数,但是您可以使用聚合函数和CASE
语句对其进行建模.对于动态版本,您将需要使用准备好的语句:
Unfortunately, MySQL does not have a PIVOT
function but you can model it using an aggregate function and a CASE
statement. For a dynamic version, you will need to use prepared statements:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when part_type = ''',
part_type,
''' then part_id end) AS part_',
part_type, '_id'
)
) INTO @sql
FROM
parts;
SET @sql = CONCAT('SELECT product_id, ', @sql, '
FROM parts
GROUP BY product_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
请参见带有演示的SQL小提琴
如果只有几列,则可以使用静态版本:
If you had only a few columns, then you can use a Static version:
select product_id,
max(case when part_type ='A' then part_id end) as Part_A_Id,
max(case when part_type ='B' then part_id end) as Part_B_Id
from parts
group by product_id
这篇关于MySQL动态枢轴的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文