mysql表进行行列转换

最新推荐文章于 2022-06-18 16:27:02 发布

weixin_33774615

最新推荐文章于 2022-06-18 16:27:02 发布

阅读量114

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/iioschina/blog/849547

版权

2019独角兽企业重金招聘Python工程师标准>>>

/*create table grade(
username varchar(50) not null,
course varchar(45) null,
score int null
)


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

select * from grade;

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



"username"	"chinese"	"math"	"english"
"admin"	"85"	"90"	"80"
"maco"	"68"	"70"	"85"

*/

转载于:https://my.oschina.net/iioschina/blog/849547