本文实例讲述了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相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》
希望本文所述对大家MySQL数据库计有所帮助。
原文链接:https://blog.csdn.net/huangyuxin_/article/details/79678719