欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL横纵表相互转化操作实现方法

程序员文章站 2022-03-06 09:29:39
本文实例讲述了mysql横纵表相互转化操作实现方法。分享给大家供大家参考,具体如下:先创建一个成绩表(纵表)create table user_score( name varchar(20), s...

本文实例讲述了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);

MySQL横纵表相互转化操作实现方法

再创建一个成绩表(横表)

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);

MySQL横纵表相互转化操作实现方法

纵表转横表

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;

MySQL横纵表相互转化操作实现方法

纵表转横表

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横纵表相互转化操作实现方法