如何在MySQL中将行数据转换为列

Question

第一个问题

我运行这个简单的代码将行数据转换成列，但它只显示第一个数据。为什么会发生这种事？

SELECT  
id_material,    
at_part_number,    
mat_desc,   
GROUP_CONCAT(equipment_name SEPARATOR ', ') As Equipment`

FROM material 

INNER JOIN equipment_list ON material.id_material=equipment_list.PKid_material           
INNER JOIN equipment ON equipment_list.PKid_equipment=equipment.id_equipment;

然后我如何使设备1，设备2，Equipmnet 3，.列？不是用分隔符，而是做一些新的列？

--------------------------------------------------+    
| id_material | mat_part_number | Equipment    
+----------------------------------------------------+   
|     1000001 | AA111           | eq1, eq2, eq3 |  
+-----------------------------------------------------+   

第二个问题

SELECT  
id_material,    
mat_part_number,   
mat_desc,`   
GROUP_CONCAT(equipment_name SEPARATOR ', ') As Equipment

FROM material 

INNER JOIN equipment_list ON material.id_material=equipment_list.PKid_material INNER JOIN equipment ON equipment_list.PKid_equipment=equipment.id_equipment;

UNION

SELECT  
id_material,    
alt_part_number,   
mat_desc,   
GROUP_CONCAT(equipment_name SEPARATOR ', ') As Equipment 

FROM material 

INNER JOIN equipment_list ON material.id_material=equipment_list.PKid_material    
INNER JOIN equipment ON equipment_list.PKid_equipment=equipment.id_equipment      
INNER JOIN alternatif_list ONmaterial.id_material=alternatif_list.PKid_material    
INNER JOIN alternatif ON alternatif.id_alternatif=alternatif_list.PKid_alternatif

ORDER BY
id_material asc;

注:材料是母表，材料表和设备表有多对多的关系，所以材料表和备用表。1种材料可以包括2种设备，2种交替(材料的设备等于交替的设备)

我想做这个

`---------------------------------------------------------------------------`   
`id_material | part_number | equipment (1) | equipment (2) | equipment (n) |`     
`---------------------------------------------------------------------------`  
`100001 | AA111 | nameeq1 |nameeq2 |`    
`100001 | AA112 | nameeq1 |nameeq2 |`    //alternatif part_number    
`100002 | AA222 | nameeq3 |    |`    
`100002 | AA221 | nameeq3 |    |`      //alternatif part_number       
`100003 | AA333 | nameeq4 |nameeq5    |`          
`100003 | AA331 | nameeq4 |nameeq5    |`     //alternatif part_number   

Answer 1

考虑到这么简单的数据

DROP TABLE IF EXISTS T;
CREATE TABLE T (ID INT,col1 varchar(3));

insert into t values (1,'aaa'),(1,'bbb'),(2,'aaa'),(3,'aaa'),(3,'bbb'),(3,'ccc');

如果你group_concat没有一个组

select id,group_concat(col1)
from t;

你明白了

+------+-------------------------+
| id   | group_concat(col1)      |
+------+-------------------------+
|    1 | aaa,bbb,aaa,aaa,bbb,ccc |
+------+-------------------------+
1 row in set (0.00 sec)

与组

select id,group_concat(col1)
from t
group by id;

你明白了

+------+--------------------+
| id   | group_concat(col1) |
+------+--------------------+
|    1 | aaa,bbb            |
|    2 | aaa                |
|    3 | aaa,bbb,ccc        |
+------+--------------------+
3 rows in set (0.00 sec)

如果您想要单独的列，并且有已知的有限数量的值，可以使用条件聚合。

select id,
         max(case when col1 = 'aaa' then col1 else '' end) as first,
         max(case when col1 = 'bbb' then col1 else '' end) as second,
         max(case when col1 = 'ccc' then col1 else '' end) as third
from t
group by id;

为了得到这个

+------+-------+--------+-------+
| id   | first | second | third |
+------+-------+--------+-------+
|    1 | aaa   | bbb    |       |
|    2 | aaa   |        |       |
|    3 | aaa   | bbb    | ccc   |
+------+-------+--------+-------+
3 rows in set (0.00 sec)

如果您不知道这些值，或者它们可能会更改，那么您可以构建一个sql语句并将其提交给动态sql。

set @sql = (
    select group_concat(concat('max(case when col1 = ', char(39),s.val,char(39),' then col1 else null end) as ', val))
    from
    (select distinct col1 as val from t order by col1) s
    )
;
set @sql = concat('select id,', @sql,' from t group by id;');   
prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;

返回以下内容

+------+------+------+------+
| id   | aaa  | bbb  | ccc  |
+------+------+------+------+
|    1 | aaa  | bbb  | NULL |
|    2 | aaa  | NULL | NULL |
|    3 | aaa  | bbb  | ccc  |
+------+------+------+------+
3 rows in set (0.00 sec)

如果您想限制列的数量，那么使用一个变量来计算最大列数，如下所示

DROP TABLE IF EXISTS T;
CREATE TABLE T (ID INT,col1 varchar(3));

insert into t values (1,'aaa'),(1,'bbb'),(2,'aaa'),(3,'aaa'),(3,'ddd'),(3,'eee');


set @sql = (
    select group_concat(distinct concat('max(case when s.rn = ', S.RN,' then col1 else null end) as Equipment_', S.RN))
    from
    (
        SELECT ID,
             IF(ID <> @P, @RN:=1,@RN:=@RN+1) RN,
            @P:=ID P
        FROM T
        CROSS JOIN (SELECT @RN:=0,@P:=0) R
    ) S
    );

set @sql = concat('select id,', @sql,' from 
(
        SELECT ID,col1,
             IF(ID <> @P, @RN:=1,@RN:=@RN+1) RN,
            @P:=ID P
        FROM T
        CROSS JOIN (SELECT @RN:=0,@P:=0) R
        order by id,col1
) S group by id;'); 

#SELECT @SQL;

prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;

+------+-------------+-------------+-------------+
| id   | Equipment_1 | Equipment_2 | Equipment_3 |
+------+-------------+-------------+-------------+
|    1 | aaa         | bbb         | NULL        |
|    2 | aaa         | NULL        | NULL        |
|    3 | aaa         | ddd         | eee         |
+------+-------------+-------------+-------------+
3 rows in set (0.00 sec)