列转行
列转行:sum(case when then else end) 新列名, 或 max() 新列名。二者结果一样(原因见下)
题目原表如下:
#记得when之后的引用加‘’
select name,
sum(case subject when 'chinese' then score else 0 end) chinese,
sum(case subject when 'math' then score else 0 end) math,
sum(case subject when 'english' then score else 0 end) english
from scores group by name;
**为什么一定要用sum()或max():
首先分步看过程:
一不加sum()或max(),也不加group by
select name,
(case subject when 'chinese' then score else 0 end) chinese,
(case subject when 'math' then score else 0 end) math,
(case subject when 'english' then score else 0 end) english
from scores_c
二不加sum()或max(),加上group by:mysql 报错!
但据他人博文(链接: Mysql case when 实现行转列时为什么要用max()或者其他聚合函数. )分析,说是此时只取了groupby分组之后的第一行。见下:
三加上sum()或max(),加上group by:列转行成功!
原因分析:
未用聚合函数时,group by分组之后,每组的第一条被取出。不符合题意。
行转列
若行转列,则原列名合并重命名一新列,且union连接。
原表:
select name,'chinese' as subject,chinese as score from scores_r
union
select name,'math' as subject,math as score from scores_r
order by name,subject;