91无码视频一区|蜜臀福利在线观看|日韩一极黄色视频|色欲人妻少妇Av一区二区|婷婷香蕉视频欧美三级片黄色|一级A片刺激高潮|国产精品污污久久|日韩黄色精品日韩久久综合网|中国一级片电影在线|超碰乱码久久久免费

mysql行列轉(zhuǎn)換mysql行列轉(zhuǎn)換

2023-06-07

mysql行列轉(zhuǎn)換


創(chuàng)建數(shù)據(jù)庫，表格


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  #成績
);

添加數(shù)據(jù)


INSERT INTO `t_score`(name,Subject,Fraction) VALUES
         ('王海'， '語文', 86),
        ('王海'， '數(shù)學(xué)', 83),
        ('王海'， '英語', 93),
        (陶俊'， '語文', 88),
        (陶俊'， '數(shù)學(xué)', 84),
        (陶俊'， '英語', 94),
        (劉可'， '語文', 80),
        (劉可'， '數(shù)學(xué)', 86),
        (劉可'， '英語', 88),
        (‘李春’， '語文', 89),
        (‘李春’， '數(shù)學(xué)', 80),
        (‘李春’， '英語', 87);

方法一：使用if


select name as 名字 ,
sum(if(Subject='語文',Fraction,0)) as 語文,
sum(if(Subject='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué), 
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(數(shù)學(xué)) Math,sum(英文) English,round(AVG(總分)，2)as 均分,sum(總分) score  from(
select 'TOTAL' as name,
sum(if(Subject='語文',Fraction,0)) as 語文,
sum(if(Subject='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué), 
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 = '數(shù)學(xué)' 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 = '數(shù)學(xué)' 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='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué),
       sum(Fraction) 總分
        from t_score group by name with rollup

分類：Mysql數(shù)據(jù)庫(二學(xué)年)，數(shù)據(jù)庫


-----------------------------------------------------------------------------------------


mysql行列轉(zhuǎn)換


創(chuàng)建數(shù)據(jù)庫，表格


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  #成績
);

添加數(shù)據(jù)


INSERT INTO `t_score`(name,Subject,Fraction) VALUES
         ('王海'， '語文', 86),
        ('王海'， '數(shù)學(xué)', 83),
        ('王海'， '英語', 93),
        (陶俊'， '語文', 88),
        (陶俊'， '數(shù)學(xué)', 84),
        (陶俊'， '英語', 94),
        (劉可'， '語文', 80),
        (劉可'， '數(shù)學(xué)', 86),
        (劉可'， '英語', 88),
        (‘李春’， '語文', 89),
        (‘李春’， '數(shù)學(xué)', 80),
        (‘李春’， '英語', 87);

方法一：使用if


select name as 名字 ,
sum(if(Subject='語文',Fraction,0)) as 語文,
sum(if(Subject='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué), 
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(數(shù)學(xué)) Math,sum(英文) English,round(AVG(總分)，2)as 均分,sum(總分) score  from(
select 'TOTAL' as name,
sum(if(Subject='語文',Fraction,0)) as 語文,
sum(if(Subject='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué), 
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 = '數(shù)學(xué)' 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 = '數(shù)學(xué)' 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='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué),
       sum(Fraction) 總分
        from t_score group by name with rollup

分類：Mysql數(shù)據(jù)庫(二學(xué)年)，數(shù)據(jù)庫








創(chuàng)建數(shù)據(jù)庫，表格


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  #成績
);

添加數(shù)據(jù)


INSERT INTO `t_score`(name,Subject,Fraction) VALUES
         ('王海'， '語文', 86),
        ('王海'， '數(shù)學(xué)', 83),
        ('王海'， '英語', 93),
        (陶俊'， '語文', 88),
        (陶俊'， '數(shù)學(xué)', 84),
        (陶俊'， '英語', 94),
        (劉可'， '語文', 80),
        (劉可'， '數(shù)學(xué)', 86),
        (劉可'， '英語', 88),
        (‘李春’， '語文', 89),
        (‘李春’， '數(shù)學(xué)', 80),
        (‘李春’， '英語', 87);

方法一：使用if


select name as 名字 ,
sum(if(Subject='語文',Fraction,0)) as 語文,
sum(if(Subject='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué), 
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(數(shù)學(xué)) Math,sum(英文) English,round(AVG(總分)，2)as 均分,sum(總分) score  from(
select 'TOTAL' as name,
sum(if(Subject='語文',Fraction,0)) as 語文,
sum(if(Subject='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué), 
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 = '數(shù)學(xué)' 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 = '數(shù)學(xué)' 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='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué),
       sum(Fraction) 總分
        from t_score group by name with rollup

分類：Mysql數(shù)據(jù)庫(二學(xué)年)，數(shù)據(jù)庫


-----------------------------------------------------------------------------------------


創(chuàng)建數(shù)據(jù)庫，表格


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  #成績
);

添加數(shù)據(jù)


INSERT INTO `t_score`(name,Subject,Fraction) VALUES
         ('王海'， '語文', 86),
        ('王海'， '數(shù)學(xué)', 83),
        ('王海'， '英語', 93),
        (陶俊'， '語文', 88),
        (陶俊'， '數(shù)學(xué)', 84),
        (陶俊'， '英語', 94),
        (劉可'， '語文', 80),
        (劉可'， '數(shù)學(xué)', 86),
        (劉可'， '英語', 88),
        (‘李春’， '語文', 89),
        (‘李春’， '數(shù)學(xué)', 80),
        (‘李春’， '英語', 87);

方法一：使用if


select name as 名字 ,
sum(if(Subject='語文',Fraction,0)) as 語文,
sum(if(Subject='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué), 
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(數(shù)學(xué)) Math,sum(英文) English,round(AVG(總分)，2)as 均分,sum(總分) score  from(
select 'TOTAL' as name,
sum(if(Subject='語文',Fraction,0)) as 語文,
sum(if(Subject='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué), 
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 = '數(shù)學(xué)' 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 = '數(shù)學(xué)' 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='數(shù)學(xué)',Fraction,0))as 數(shù)學(xué),
       sum(Fraction) 總分
        from t_score group by name with rollup

分類：Mysql數(shù)據(jù)庫(二學(xué)年)，數(shù)據(jù)庫


本文僅代表作者觀點，版權(quán)歸原創(chuàng)者所有，如需轉(zhuǎn)載請在文中注明來源及作者名字。

免責(zé)聲明：本文系轉(zhuǎn)載編輯文章，僅作分享之用。如分享內(nèi)容、圖片侵犯到您的版權(quán)或非授權(quán)發(fā)布，請及時與我們聯(lián)系進(jìn)行審核處理或刪除，您可以發(fā)送材料至郵箱：service@tojoy.com