mysql怎么做纵表_MySQL如何实现横纵表相互转化

MySQL如何实现横纵表相互转化

发布时间：2020-07-18 15:12:52

来源：亿速云

阅读：68

作者：小猪

这篇文章主要讲解了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;

看完上述内容，是不是对MySQL如何实现横纵表相互转化有进一步的了解，如果还想学习更多内容，欢迎关注亿速云行业资讯频道。