欧美三级电影完整|亚洲一二三四久久|性爱视频精品一区二区免费在线观看|国产精品啪啪视频|婷婷六月综合操人妻视频网站|99爱免费视频在线观看|美女一级片在线观看|北京熟女88av|免费看黄色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ù)庫


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

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