带有动态列的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 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  |

+---------------+---------------+--------+---------+--------+

一切正常工作：)

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

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 ;