现有一张竖表table2
要将上表转成如下横表table2
此时要用到mysql的case when
select username,
sum(case course when '语文' then grade else 0) as chinese,
sum(case course when '数学' then grade else 0) as math,
sum(case course when '英语' then grade else 0) as english
from table1 group by username;
现在我们想把横表变为竖表如下
此时要用到union all
select username,'chinese' as course,chinese as grade from table2
union all
select username,'math' as course,math as grade from table2
union all
select username,'english' as course,english as grade from table2
order by username,course desc;
延伸:两张表横向连接使用join