我使用下表来存储产品数据：
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 | +---------------+---------------+--------+---------+--------+
一切正常:)
因为我动态填充“附加”表，如果查询也是动态的，那就太好了。这样我就不必每次输入新的字段名和字段值时都更改查询。
我的做法与接受的答案略有不同。这样您就可以避免使用 GROUP_CONCAT，它默认限制为 1024 个字符，如果您有很多字段，则该限制将不起作用，除非您更改限制。
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, p.name, p.description'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;点击此处查看演示
MySQL 中动态执行此操作的唯一方法是使用准备好的语句。这是一篇关于它们的好文章：
动态数据透视表（将行转换为列）
您的代码将如下所示：
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, p.name, p.description'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;参见演示
注意：GROUP_CONCAT 函数的长度限制为 1024 个字符。查看参数group_concat_max_len