mySql数据库表行列转换 - 数据库编程

/*创建表格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;