带有动态列的 MySQL 数据透视表查询

MySQL pivot table query with dynamic columns

提问by fr0sty

I'm using the following tables for storing product data:

我使用下表来存储产品数据：

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      |
+-----------------+------------+

Using the following query to select the records from both tables

使用以下查询从两个表中选择记录

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  |
+---------------+---------------+--------+---------+--------+

And everything is working correctly :)

一切正常:)

Because i fill the 'additional' table dynamically it would be nice, if the query would also be dynamic. In that way i dont have to change the query everytime i put in a new fieldname and fieldvalue.

因为我动态填充“附加”表，如果查询也是动态的，那就太好了。这样，每次我输入新的字段名和字段值时，我都不必更改查询。

回答by Taryn

The only way in MySQL to do this dynamically is with Prepared statements. Here is a good article about them:

MySQL 中动态执行此操作的唯一方法是使用 Prepared 语句。这是一篇关于它们的好文章：

Dynamic pivot tables (transform rows to columns)

动态数据透视表（将行转换为列）

Your code would look like this:

您的代码如下所示：

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;

See Demo

看演示

NOTE: GROUP_CONCAT function has a limit of 1024 characters. See parameter group_concat_max_len

注意：GROUP_CONCAT 函数有 1024 个字符的限制。参见参数 group_concat_max_len

回答by Curtis

I have a slightly different way of doing this than the accepted answer. This way you can avoid using GROUP_CONCAT which has a limit of 1024 characters and will not work if you have a lot of fields.

我的做法与接受的答案略有不同。通过这种方式，您可以避免使用 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');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

回答by Richard

Here's stored procedure, which will generate the table based on data from one table and column and data from other table and column.

这是存储过程，它将根据来自一个表和列的数据以及来自其他表和列的数据生成表。

The function 'sum(if(col = value, 1,0)) as value ' is used. You can choose from different functions like MAX(if()) etc.

使用函数 'sum(if(col = value, 1,0)) as value '。您可以选择不同的函数，如 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 ;