mysql - 将垂直表转换为水平表

Question

我正在为这个操作寻找一个干净、快速和漂亮的解决方案。首先我有一个看起来像这样的表

field_id   -    item_id   -   value
   1        -     781  -       name1
   2        -     781   -      lastname1
   3        -     781   -      phone1
   1        -     782   -      name2
   2        -     782   -      lastname2
   3        -     782   -      phone2
   1        -     783   -      name3
   2        -     783   -      lastname3
   3        -     783   -      phone3

我想像这样显示这个表:

item_id    -    name   -   lastname   -   phone
781     -        name1    -  lastname1   -   phone1
782     -        name2    -  lastname2   -   phone2   
783     -        name3    -  lastname3   -   phone3

我试过了

SELECT field_id, item_id,
(CASE WHEN field_id = 1 THEN value END) AS name,
(CASE WHEN field_id = 2 THEN value END) AS lastname,
(CASE WHEN field_id = 3 THEN value END) AS phone
FROM cq6xb_fields_values
GROUP BY item_id;

但是我得到了这个奇怪的结果

item_id    -    name   -   lastname   -   null 
781     -        null -  lastname1   -   null 
782     -        null -  lastname2   -   null 
783     -        null -  lastname3   -   null 

Answer 1

你不远只是添加 max 并且你可以删除字段 id 因为你没有使用它。

SELECT item_id,
max(CASE WHEN field_id = 1 THEN value END) AS name,
max(CASE WHEN field_id = 2 THEN value END) AS lastname,
max(CASE WHEN field_id = 3 THEN value END) AS phone
FROM cq6xb_fields_values
GROUP BY item_id;

+---------+-------+-----------+--------+
| item_id | name  | lastname  | phone  |
+---------+-------+-----------+--------+
|     781 | name1 | lastname1 | phone1 |
|     782 | name2 | lastname2 | phone2 |
|     783 | name3 | lastname3 | phone3 |
+---------+-------+-----------+--------+
3 rows in set (0.00 sec)