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相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》

希望本文所述对大家MySQL数据库计有所帮助。

更多相关文章

随机推荐