mySql 数据库表行列转换

最新推荐文章于 2021-03-18 00:08:12 发布

回_憶

最新推荐文章于 2021-03-18 00:08:12 发布

阅读量725

点赞数

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/angjunqiang/article/details/42150565

版权

数据库专栏收录该内容

12 篇文章

订阅专栏

/* 创建表格grade*/

crate table grade (
  username varchar(45) not null ,
  cource varchar(45) null ,
  score int null);

/* 插入数据*/

insert into grade (`username`, `cource`, `score`) values ('admin', 'english', '80');
insert into grade (`username`, `cource`, `score`) values ('admin', 'math', '90');
insert into grade (`username`, `cource`, `score`) values ('maco', 'english', '85');
insert into grade (`username`, `cource`, `score`) values ('admin', 'chinese', '85');
insert into grade (`username`, `cource`, `score`) values ('maco', 'math', '70');
insert into grade (`username`, `cource`, `score`) values ('maco', 'chinese', '68');

select * from grade;

/* 查询行列转换结果*/

select  username,
      sum(if(cource='chinese',score,0)) as  chinese,
      sum(if(cource='math',score,0)) as  math,
      sum(if(cource='english',score,0)) as  english from grade group by username;