\u6211\u6709\u4e00\u5f20\u8fd9\u6837\u7684\u4ea7\u54c1\u96f6\u4ef6\u8868\uff1a<\/p>\n\n\n\n
part_id part_type product_id<\/p>\n\n\n\n\n\n\n\n
————————————–<\/p>\n\n\n\n
1 A 1<\/p>\n\n\n\n
2 B 1<\/p>\n\n\n\n
3 A 2<\/p>\n\n\n\n
4 B 2<\/p>\n\n\n\n
5 A 3<\/p>\n\n\n\n
6 B 3<\/p>\n\n\n\n
\u6211\u60f3\u8981\u4e00\u4e2a\u8fd4\u56de\u5982\u4e0b\u8868\u683c\u7684\u67e5\u8be2\uff1a<\/p>\n\n\n\n
product_id part_A_id part_B_id
—————————————-<\/p>\n\n\n\n
1 1 2
2 3 4
3 5 6<\/p>\n\n\n\n
\u5728\u5b9e\u9645\u5b9e\u65bd\u4e2d,\u5c06\u6709\u6570\u767e\u4e07\u4e2a\u4ea7\u54c1\u90e8\u4ef6<\/p>\n\n\n\n
\u6700\u4f73\u7b54\u6848<\/p>\n\n\n\n
\u9057\u61be\u7684\u662f,MySQL\u6ca1\u6709PIVOT\u529f\u80fd,\u4f46\u60a8\u53ef\u4ee5\u4f7f\u7528\u805a\u5408\u51fd\u6570\u548cCASE\u8bed\u53e5\u5bf9\u5176\u8fdb\u884c\u5efa\u6a21.\u5bf9\u4e8e\u52a8\u6001\u7248\u672c,\u60a8\u9700\u8981\u4f7f\u7528\u9884\u51c6\u5907\u8bed\u53e5\uff1a<\/p>\n\n\n\n
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT(
‘max(case when part_type = ”’,part_type,”’ then part_id end) AS part_’,’_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;<\/p>\n\n\n\n
\u5982\u679c\u60a8\u53ea\u6709\u51e0\u5217,\u90a3\u4e48\u60a8\u53ef\u4ee5\u4f7f\u7528\u9759\u6001\u7248\u672c\uff1a<\/p>\n\n\n\n
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<\/p>\n","protected":false},"excerpt":{"rendered":"
\u6211\u6709\u4e00\u5f20\u8fd9\u6837\u7684\u4ea7\u54c1\u96f6\u4ef6\u8868\uff1a part_id part_type & … <\/p>\n