mysql透视表编写方法

Question

mysql数据库，有以下数据表（table1），当然是巨量的，我需要生成后边的结果，生成一个新表（table2）。

日期 名称 值 值2
2022/3/25 A 48.62 z
2022/3/25 B 37.14 q
2022/3/25 C 7.97 w
2022/3/25 D 18.64 r
2022/3/25 F 23.57 t
2022/3/26 A 6.92 z
2022/3/26 B 45.5 q
2022/3/26 C 12.58 w
2022/3/26 D 29.04 r
2022/3/26 F 18.35 t
2022/3/27 A 53.5 z
2022/3/27 B 23.65 q
2022/3/27 C 4.02 w
2022/3/27 D 4.03 r
2022/3/27 F 5.34 t
2022/3/28 A 5.02 z
2022/3/28 B 5.57 q
2022/3/28 C 5.68 w
2022/3/28 D 7.43 r
2022/3/28 F 6.12 t

我需要以下结果，生成另一个表（table2）：

名称 值2 2022/3/25 2022/3/26 2022/3/27 2022/3/28
A z 48.62 6.92 53.5 5.02
B q 37.14 45.5 23.65 5.57
C w 7.97 12.58 4.02 5.68
D r 18.64 29.04 4.03 7.43
F t 23.57 18.35 5.34 6.12

谢谢！

因为是数据量较大的，不能够罗列日期等，需要用取值方式，自动取值，自动生成。

Answer 1

/*建表*/ 
create table T
(
 riqi DATE ,
 mc varchar(10),
 z numeric(18,2),
 z2 VARCHAR(10)
)
/*添加测试数据*/
insert into T  values('2022/3/25', 'A', 48.62 ,'z');
insert into T  values('2022/3/25', 'B', 37.14 ,'q');
insert into T  values('2022/3/25', 'C', 7.97 ,'w');
insert into T  values('2022/3/25', 'D', 18.64 ,'r');
insert into T  values('2022/3/25', 'F', 23.57 ,'t');
insert into T  values('2022/3/26', 'A', 6.92 ,'z');
insert into T  values('2022/3/26', 'B', 45.5 ,'q');
insert into T  values('2022/3/26', 'C', 12.58 ,'w');
insert into T  values('2022/3/26', 'D', 29.04 ,'r');
insert into T  values('2022/3/26', 'F', 18.35 ,'t');
insert into T  values('2022/3/27', 'A', 53.5 ,'z');
insert into T  values('2022/3/27', 'B', 23.65 ,'q');
insert into T  values('2022/3/27', 'C', 4.02 ,'w');
insert into T  values('2022/3/27', 'D', 4.03 ,'r');
insert into T  values('2022/3/27', 'F', 5.34 ,'t');
insert into T  values('2022/3/28', 'A', 5.02 ,'z');
insert into T  values('2022/3/28', 'B', 5.57 ,'q');
insert into T  values('2022/3/28', 'C', 5.68 ,'w');
insert into T  values('2022/3/28', 'D', 7.43 ,'r');
insert into T  values('2022/3/28', 'F', 6.12 ,'t');

/*拼接行转列SQL*/
set @sql = '';

select GROUP_CONCAT('max(case when riqi=''',riqi,''' then z else null end) as `',  riqi,'`')  into @sql   from 
(
    select riqi  from T group by riqi
) a ;
set  @sql =CONCAT('select mc,z2,',@sql,' from t group  by mc,z2');
 
 
/*执行SQL,得到结果*/ 
PREPARE stmt FROM @sql;       
EXECUTE stmt ;        
deallocate prepare stmt;      

/*删除表*/
drop  table T