日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

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

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

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

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

②動態(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)一維

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

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