mysql行列转换

1.一维转二维

 

上图为成绩表中数据，现希望将数据转换为下图。

 

①静态：转化为二维表后的列名及列数是确定不变的，本例中即course只有数学、语文、英语这三门课。

select s_name,
max(if(course="数学",score,0)) as 数学,
max(if(course='语文',score,0)) as 语文,
max(if(course='英语',score,0)) as 英语,
sum(score) as 总分
from grade group by s_name;

 

②动态：转化为二维表后的列名及列数是可变的，本例中即course的课程数不确定。

set @sql='';
select@sql:=concat(@sql,'max(if(course='',course,'',score,0)) as ',course,',')from (select distinct course from grade) as a;
set@strsql=concat('select s_name,',@sql,'sum(score)as 总分 from grade group by s_name;');
prepare stmt from @strsql;
execute stmt;
deallocate prepare stmt;

 

2.二维转一维

 

上图为成绩表2中数据，现希望将数据转为成绩表1的数据。

 

select name,'数学'as course,数学 as score from grade2
union all
select name,'语文'as course,语文 as score from grade2
union all
select name,'英语'as course,英语 as score from grade2
order by name;

 

胜象大百科