亚洲欧美日韩熟女|做爱高潮视频网址|国产一区二区三级片|国产Av中文字幕www.性色av|亚洲婷婷永久免费|国产高清中文字幕|欧美变态网站久re视频精品|人妻AV鲁丝第一页|天堂AV一区二区在线观看|综合 91在线精品

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ù)學', 83),
        ('王海'， '英語', 93),
        (陶俊'， '語文', 88),
        (陶俊'， '數(shù)學', 84),
        (陶俊'， '英語', 94),
        (劉可'， '語文', 80),
        (劉可'， '數(shù)學', 86),
        (劉可'， '英語', 88),
        (‘李春’， '語文', 89),
        (‘李春’， '數(shù)學', 80),
        (‘李春’， '英語', 87);

方法一：使用if


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

分類：Mysql數(shù)據(jù)庫(二學年)，數(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ù)學', 83),
        ('王海'， '英語', 93),
        (陶俊'， '語文', 88),
        (陶俊'， '數(shù)學', 84),
        (陶俊'， '英語', 94),
        (劉可'， '語文', 80),
        (劉可'， '數(shù)學', 86),
        (劉可'， '英語', 88),
        (‘李春’， '語文', 89),
        (‘李春’， '數(shù)學', 80),
        (‘李春’， '英語', 87);

方法一：使用if


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

分類：Mysql數(shù)據(jù)庫(二學年)，數(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ù)學', 83),
        ('王海'， '英語', 93),
        (陶俊'， '語文', 88),
        (陶俊'， '數(shù)學', 84),
        (陶俊'， '英語', 94),
        (劉可'， '語文', 80),
        (劉可'， '數(shù)學', 86),
        (劉可'， '英語', 88),
        (‘李春’， '語文', 89),
        (‘李春’， '數(shù)學', 80),
        (‘李春’， '英語', 87);

方法一：使用if


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

分類：Mysql數(shù)據(jù)庫(二學年)，數(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ù)學', 83),
        ('王海'， '英語', 93),
        (陶俊'， '語文', 88),
        (陶俊'， '數(shù)學', 84),
        (陶俊'， '英語', 94),
        (劉可'， '語文', 80),
        (劉可'， '數(shù)學', 86),
        (劉可'， '英語', 88),
        (‘李春’， '語文', 89),
        (‘李春’， '數(shù)學', 80),
        (‘李春’， '英語', 87);

方法一：使用if


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

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


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

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