mysql> SELECT * FROM product;
+---------------+---------------+--------+
| id | name | description | stock |
+---------------+---------------+--------+
| 1 | product1 | first product | 5 |
| 2 | product2 | second product| 5 |
+---------------+---------------+--------+
mysql> SELECT * FROM product_additional;
+-----------------+------------+
| id | fieldname | fieldvalue |
+-----------------+------------+
| 1 | size | S |
| 1 | height | 103 |
| 2 | size | L |
| 2 | height | 13 |
| 2 | color | black |
+-----------------+------------+
使用以下查询从两个表中选择记录
mysql> SELECT
p.id
, p.name
, p.description
,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) as `size`
,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) as `height`
,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) as `color`
FROM product p
LEFT JOIN product_additional AS pa ON p.id = pa.id
GROUP BY p.id
+---------------+---------------+--------+---------+--------+
| id | name | description | size | height | color |
+---------------+---------------+--------+---------+--------+
| 1 | product1 | first product | S | 103 | null |
| 2 | product2 | second product| L | 13 | black |
+---------------+---------------+--------+---------+--------+
一切正常：)
因为我动态地填充了'additional'表，所以如果查询也是动态的，那就很好了。这样，我不必在每次输入新的字段名和字段值时都更改查询。
#1 楼
MySQL中动态执行此操作的唯一方法是使用Prepared语句。这是一篇关于它们的好文章：动态数据透视表（将行转换为列）
您的代码如下所示：
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(pa.fieldname = ''',
fieldname,
''', pa.fieldvalue, NULL)) AS ',
fieldname
)
) INTO @sql
FROM product_additional;
SET @sql = CONCAT('SELECT p.id
, p.name
, p.description, ', @sql, '
FROM product p
LEFT JOIN product_additional AS pa
ON p.id = pa.id
GROUP BY p.id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
请参阅演示
注意：GROUP_CONCAT函数的限制为1024个字符。参见参数group_concat_max_len
评论
感谢您的快速回答！因此，在PHP中这意味着我要进行2个查询。一种用于获取列的字符串，第二种用于将字符串连接到完整查询中？在这里使用Zend Framework。
–fr0sty
2012-09-26 12:21
@ fr0sty，您将不得不在PHP中对其进行测试。我不确定您将如何继续执行该代码。
– Taryn♦
2012年9月26日在12:22
我只是使用了此示例，然后重新应用了该示例以从wp_posts和wp_postmeta中提取数据，这是一个巨大的帮助。谢谢。我唯一需要添加的是对SET group_concat_max_len = 150000的调用；因此它不会发出警告。
– Firegarden
15年1月14日在8:17
这是一个很大的帮助。我将这种方法应用于类似的问题，而不是连接SELECT语句，而是创建了一个每隔几个小时安排一次的事件，以重建一个视图，该视图将一个表中的n行数据透视成另一个表中的n列。这是一个很大的帮助，因为在我每次执行SELECT时都使用PHP重建查询之前。即使视图不能利用索引，但我认为过滤性能不会成为问题，因为枢轴行->列表示特许经营员工的培训类型，因此视图永远不会破坏几千行。谢谢@bluefeet！
– OnethingSimple
15年8月16日在16:28
@JuanCarlosOropeza感谢您告诉我，我用db <> fiddle替换了链接。
– Taryn♦
19年8月7日在14:21
#2 楼
与接受的答案相比，我的执行方式略有不同。这样，您可以避免使用限制为1024个字符的GROUP_CONCAT，并且如果您有很多字段，则将无法使用。SET @sql = '';
SELECT
@sql := CONCAT(@sql,if(@sql='','',', '),temp.output)
FROM
(
SELECT
DISTINCT
CONCAT(
'MAX(IF(pa.fieldname = ''',
fieldname,
''', pa.fieldvalue, NULL)) AS ',
fieldname
) as output
FROM
product_additional
) as temp;
SET @sql = CONCAT('SELECT p.id
, p.name
, p.description, ', @sql, '
FROM product p
LEFT JOIN product_additional AS pa
ON p.id = pa.id
GROUP BY p.id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
评论
我在mySQL工作台中运行此程序时收到错误。如何运行？
–邓恩
16年8月4日在10:06
好对我有用。这将取决于您遇到什么错误
–柯蒂斯
16年8月24日在16:01
最后，一个真实的答案！谢谢，的确，我在使用GROUP_CONCAT和许多字段时遇到了麻烦。非常感谢！！
– Metafaniel
17-4-24在17:46
通过定义group_concat的限制，SET SESSION group_concat_max_len = 1000000;以上设置为SESSION SET GLOBAL group_concat_max_len = 1000000;
–米格尔
18年6月26日在15:32
#3 楼
这是存储过程，它将基于一个表和一个列中的数据以及另一表和列中的数据生成表。函数'sum（if（col（value = 1,0，））as值'被使用。您可以从MAX（if（））等不同函数中进行选择。
delimiter //
create procedure myPivot(
in tableA varchar(255),
in columnA varchar(255),
in tableB varchar(255),
in columnB varchar(255)
)
begin
set @sql = NULL;
set @sql = CONCAT('select group_concat(distinct concat(
\'SUM(IF(',
columnA,
' = \'\'\',',
columnA,
',\'\'\', 1, 0)) AS \'\'\',',
columnA,
',\'\'\'\') separator \', \') from ',
tableA, ' into @sql');
-- select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- select @sql;
SET @sql = CONCAT('SELECT p.',
columnB,
', ',
@sql,
' FROM ', tableB, ' p GROUP BY p.',
columnB,'');
-- select @sql;
/* */
PREPARE stmt FROM @sql;
EXECUTE stmt;
/* */
DEALLOCATE PREPARE stmt;
end//
delimiter ;
评论
MySQL枢轴行可能复制为动态列数