一、列转行:
列数据:
转换后行数据:
方法:通过union all 实现
select userid, '语文' as subjectName ,chinese_score as socre from score
union all
select userid, '数学' as subjectName ,math_score as socre from score
union all
select userid, '英语' as subjectName ,english_score as socre from score
union all
select userid, '政治' as subjectName ,politics_score as socre from score
二、行转列:
行数据:
转化后列数据:
方法一:case when … then …
select userid,
sum(case when subjectName='语文' then score else 0 end )as '语文',
sum(case when subjectName='数学' then score else 0 end )as '数学',
sum(case when subjectName='英语' then score else 0 end )as '英语',
sum(case when subjectName='政治' then score else 0 end )as '政治'
from score
group by userid;
方法二:sum(if())
select userid,
sum(if(subjectName='语文' , score , 0 ))as '语文',
sum(if(subjectName='数学' , score , 0 ))as '数学',
sum(if(subjectName='英语' , score , 0 ))as '英语',
sum(if(subjectName='政治' , score , 0 ))as '政治'
from score
group by userid;
[参考文章]:
http://www.360doc.com/content/21/0614/01/37253146_981925362.shtml