MySQL横纵表相互转化如何实现?本篇文章小编给大家分享一下MySQL横纵表相互转化实现方法,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
先创建一个成绩表(纵表)
create table user_score ( name varchar(20), subjects varchar(20), score int ); insert into user_score(name,subjects,score) values('张三','语文',60); insert into user_score(name,subjects,score) values('张三','数学',70); insert into user_score(name,subjects,score) values('张三','英语',80); insert into user_score(name,subjects,score) values('李四','语文',90); insert into user_score(name,subjects,score) values('李四','数学',100);
再创建一个成绩表(横表)
create table user_score2 ( name varchar(20), yuwen int, shuxue int, yingyu int ); insert into user_score2(name,yuwen,shuxue,yingyu) values('张三',60,70,80); insert into user_score2(name,yuwen,shuxue,yingyu) values('李四',90,100,0);
纵表转横表
select name,sum(case subjects when '语文' then score else 0 end) as '语文',sum(case subjects when '数学' then score else 0 end) as '数学', sum(case subjects when '英语' then score else 0 end) as '英语'from user_score group by name;
纵表转横表
SELECT name,'yuwen' AS subjects,yuwen AS score FROM user_score2 UNION ALL SELECT name,'shuxue' AS subjects,shuxue AS score FROM user_score2 UNION ALL SELECT name,'yingyu' AS subjects,yingyu AS score FROM user_score2 ORDER BY name,subjects DESC;