sql - 将 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 属性)，它可以让您将同一属性的多个值连接到一个列表中。