1.行列转换
表b1:
表b2:
行转列,也就是b1->b2,思路是按学生分组后组内分别求各科成绩。
{
select
name as '姓名',
max(if(course='语文',score,0)) as '语文', -- 也可以用sum
max(if(course='数学',score,0)) as '数学',
max(if(course='英语',score,0)) as '英语'
from b1
group by name;
}
列转行,b2->b1,思路是分别选出所有人的各科成绩,将各科成绩做个union all合并操作。
{
select
姓名 as name,
'语文' as course, -- 带引号表示此处按字符串对待,而不是标识符
语文 as score
from b2
union all
select
姓名 as name,
'数学' as course,
数学 as score
from tb2
union all
select
姓名 as name,
'英语' as course,
英语 as score
from b2;
}