mysql行列转换[转]

mysql行列转换[转]

 

创建数据库、表

复制代码
create database tests; use tests; create table t_score(
id int primary key auto_increment,
name varchar(20) not null,  #名字
Subject varchar(10) not null, #科目
Fraction double default 0 #分数
);
复制代码

添加数据

复制代码
INSERT INTO `t_score`(name,Subject,Fraction) VALUES ('王海', '语文', 86),
        ('王海', '数学', 83),
        ('王海', '英语', 93),
        ('陶俊', '语文', 88),
        ('陶俊', '数学', 84),
        ('陶俊', '英语', 94),
        ('刘可', '语文', 80),
        ('刘可', '数学', 86),
        ('刘可', '英语', 88),
        ('李春', '语文', 89),
        ('李春', '数学', 80),
        ('李春', '英语', 87);
复制代码

 方式一:使用if

复制代码
select name as 名字 , sum(if(Subject='语文',Fraction,0)) as 语文, sum(if(Subject='数学',Fraction,0))as 数学, sum(if(Subject='英语',Fraction,0))as 英语, round(AVG(Fraction),2) as 平均分, SUM(Fraction) as 总分 from t_score group by name union select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from( select 'TOTAL' as name, sum(if(Subject='语文',Fraction,0)) as 语文, sum(if(Subject='数学',Fraction,0))as 数学, sum(if(Subject='英语',Fraction,0))as 英语, SUM(Fraction) as 总分 from t_score group by Subject )t
复制代码

方式二:使用case

复制代码
select name as Name, sum(case when Subject = '语文' then Fraction end) as Chinese, sum(case when Subject = '数学' then Fraction end) as Math, sum(case when Subject = '英语' then Fraction end) as English, sum(fraction)as score from t_score group by name UNION ALL select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from( select 'TOTAL' as name, sum(case when Subject = '语文' then Fraction end) as Chinese, sum(case when Subject = '数学' then Fraction end) as Math, sum(case when Subject = '英语' then Fraction end) as English, sum(fraction)as score from t_score group by Subject)t
复制代码

方法三: with rollup

复制代码
 select ifnull(name,'TOll') name, sum(if(Subject='语文',Fraction,0)) as 语文, sum(if(Subject='英语',Fraction,0)) as 英语, sum(if(Subject='数学',Fraction,0))as 数学, sum(Fraction) 总分 from t_score group by name with rollup
复制代码

摘自:https://www.cnblogs.com/weibanggang/p/9679301.html

mysql行列转换[转]