189 8069 5689
这篇文章主要讲解了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如何实现横纵表相互转化有进一步的了解，如果还想学习更多内容，欢迎关注创新互联行业资讯频道。