将 MySQL 结果集从行转换为列

Question

我有这样的选择输出：

04:47:37> select * from attributes left outer join trailer_attributes on attributes.id = trailer_attributes.attribute_id;
+----+--------------+----------+-----------+------------+--------------+-----------------+
| id | name         | datatype | list_page | trailer_id | attribute_id | attribute_value |
+----+--------------+----------+-----------+------------+--------------+-----------------+
|  1 | Make         | text     |         1 |          1 |            1 | Apple           | 
|  1 | Make         | text     |         1 |          2 |            1 | sdfg            | 
|  2 | Year         | number   |         1 |          1 |            2 | 2009            | 
|  2 | Year         | number   |         1 |          2 |            2 | sdfg            | 
|  3 | Type         | text     |         0 |          1 |            3 | iPhone          | 
|  3 | Type         | text     |         0 |          2 |            3 | sdfg            | 
|  4 | Axles        | text     |         0 |          1 |            4 | asdf            | 
|  4 | Axles        | text     |         0 |          2 |            4 | sdfg            | 
|  7 | Size         | text     |         0 |          1 |            7 | asd1            | 
|  7 | Size         | text     |         0 |          2 |            7 | sdfg            | 
|  8 | Frame        | text     |         0 |          1 |            8 |                 | 
|  8 | Frame        | text     |         0 |          2 |            8 | sdfg            | 
|  9 | Height       | text     |         0 |          1 |            9 |                 | 
|  9 | Height       | text     |         0 |          2 |            9 | sdfg            | 
| 10 | Dollies      | text     |         0 |          1 |           10 |                 | 
| 10 | Dollies      | text     |         0 |          2 |           10 | sdfg            | 
| 11 | Tires/Wheels | text     |         0 |          1 |           11 |                 | 
| 11 | Tires/Wheels | text     |         0 |          2 |           11 | sdfg            | 
| 12 | Condition    | text     |         1 |          1 |           12 | New             | 
| 12 | Condition    | text     |         1 |          2 |           12 | sdfg            | 
| 13 | Title        | text     |         0 |          1 |           13 |                 | 
| 13 | Title        | text     |         0 |          2 |           13 | sdfg            | 
+----+--------------+----------+-----------+------------+--------------+-----------------+

我想将其转换为更多的内容：

id, Make,  Year, Type,   Axles, Size, Frame (etc)
1,  Apple, 2009, iPhone, .....
2,  sdfg,  sdfg, sdfg,   .....

有什么建议吗？

Answer 1

嗯...EAV。避免 EAV（实体属性值）的众多原因之一是它们更难报告和查询。然而，如果你想要的属性是提前知道的，你可以这样做：

Select id
    , Min( Case When name = 'Make' Then attribute_value End ) As Make
    , Min( Case When name = 'Year' Then attribute_value End ) As Year
    , Min( Case When name = 'Type' Then attribute_value End ) As Type
    , Min( Case When name = 'Axles' Then attribute_value End ) As Axles
    , Min( Case When name = 'Size' Then attribute_value End ) As Size
    , Min( Case When name = 'Frame' Then attribute_value End ) As Frame
    , ...
From attributes
Where name In('Make','Year','Type','Axles','Size','Frame',....)
Group By id

现在，MySQL 确实有一个 GROUP_CONCAT，如果你允许的话（例如，如果一个实体），它可以让你将同一属性的多个值连接到一个列表中可以有多个 Make 属性）。

Answer 2

这可能不适合您，但理想情况下您应该将每个属性转换为主表的一列。关系数据库旨在将属性作为列而不是行来处理。因此，当您像这样使用它们时，它们的性能会更好，并且 SQL 也会变得更简单。