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

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

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

 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