亚洲视频二区_亚洲欧洲日本天天堂在线观看_日韩一区二区在线观看_中文字幕不卡一区

1.一維轉(zhuǎn)二維

上圖為成績(jī)表中數(shù)據(jù)，現(xiàn)希望將數(shù)據(jù)轉(zhuǎn)換為下圖。

①靜態(tài)：轉(zhuǎn)化為二維表后的列名及列數(shù)是確定不變的，本例中即course只有數(shù)學(xué)、語文、英語這三門課。

select s_name,
max(if(course="數(shù)學(xué)",score,0)) as 數(shù)學(xué),
max(if(course='語文',score,0)) as 語文,
max(if(course='英語',score,0)) as 英語,
sum(score) as 總分
from grade group by s_name;

②動(dòng)態(tài)：轉(zhuǎn)化為二維表后的列名及列數(shù)是可變的，本例中即course的課程數(shù)不確定。

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.二維轉(zhuǎn)一維

上圖為成績(jī)表2中數(shù)據(jù)，現(xiàn)希望將數(shù)據(jù)轉(zhuǎn)為成績(jī)表1的數(shù)據(jù)。

select name,'數(shù)學(xué)'as course,數(shù)學(xué) 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;