带有动态列的MySQL枢轴表查询

Question

带有动态列的MySQL枢轴表查询我使用以下表格存储产品数据：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 pLEFT JOIN product_additional AS pa ON p.id = pa.idGROUP BY p.id+---------------+---------------+--------+---------+--------+| id | name     | description   | size   | height  | color  |+---------------+---------------+--------+---------+--------+|  1 | product1 | first product | S      | 103     | null   ||  2 | product2 | second product| L      | 13      | black  |+---------------+---------------+--------+---------+--------+一切正常工作：)因为我动态地填充了“附加”表，所以如果查询也是动态的，那就更好了。这样，每次输入新的字段名和字段值时，我都不必更改查询。

Answer 1

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

函数‘sum(if(coll=value，1，0)为值’。您可以从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 ;