mysql行列转换mysql行列转换

2023-06-07

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

分类：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

分类：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

分类：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

分类：Mysql数据库(二学年)，数据库

本文仅代表作者观点，版权归原创者所有，如需转载请在文中注明来源及作者名字。

免责声明：本文系转载编辑文章，仅作分享之用。如分享内容、图片侵犯到您的版权或非授权发布，请及时与我们联系进行审核处理或删除，您可以发送材料至邮箱：service@tojoy.com

延伸阅读

分析中国咖啡市场行业数据：顾客将选择200-300ML，41.5%

美国特斯拉召回137辆汽车：行驶中方向盘有掉落的危险

新的韦伯望眼镜图揭示恒星诞生。

分析中国夜间经济产业数据：顾客将选择72.4%的大型商场

文旅融合频“出圈” “旅游+”消费迎来新增长点

项目推荐

康小虎 · 健康小屋

蓝丝带

毛加健康