IT源码网

mysql行列转换讲解

xmjava 2021年02月13日 数据库 164 0

 

创建数据库、表

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

 

发布评论

分享到:

IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

MySQL 中while loop repeat 的基本用法讲解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。