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

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;