mysql - 插入..选择，同一列不同值

Question

我有这张表，其中包含以下内容:

part_id  |  feature_name  | feature_value  |  feature_unit  |
_____________________________________________________________
   1     |     Weight     |      2         |       kg       |
   1     |     Color      |     Blue       |                |
   1     |   Description  |description here|                |

我想做的是将它们放在另一个(新)表中

part_id  |   description  |  color  | weight  |
_______________________________________________
   1     |description here|   Blue  |   2kg   |

我想根据 feature_value 列的值使用 Insert..Select，但似乎无法为此构建正确的查询。有什么想法吗？

Answer 1

您要将列转换为行，您可以使用基于案例的聚合来实现此目的。

INSERT into newTable(part_id, description, color, weight)
SELECT part_id
       max( case when feature_name ='Description' 
                 then feature_value end ) as description,
       max( case when feature_name ='Color' 
                 then feature_value end ) as Color,
       max( case when feature_name ='weight' 
                 then concat(feature_value,feature_unit) end ) as weight
FROM my_table
GROUP BY part_id