|  | 
 
| 原始插入数据如下:; j" P0 }& \# N4 ]% | 要求查询结果如下 :8 ]& s  h3 h( s4 C" w: M+ h9 I
 
 & d% R0 m) W% ?: I4 ]* \8 i1 z6 G+ u, K" e
 创建数据库、表4 a3 u. J; q5 q0 T
 
 复制代码create database tests;) w4 S9 H- K6 U
use tests;/ m/ ?/ A; G: |# G+ m3 }$ R
create table t_score(' m3 I/ p0 ]6 q- O
id int primary key auto_increment,. p) U+ b- e: C9 O
name varchar(20) not null,  #名字2 x" d  `  P; N
Subject varchar(10) not null, #科目+ A) B- g" C3 T- `
Fraction double default 0  #分数, L6 f4 o) B5 G
);
添加数据 复制代码INSERT INTO `t_score`(name,Subject,Fraction) VALUES. J' |5 i( X  k/ @. K7 w$ h2 Z
         ('王海', '语文', 86),3 c) t: Y' V7 d  t3 G' E6 e4 F9 l
        ('王海', '数学', 83),5 E. R* c- H3 s3 Z0 h$ [
        ('王海', '英语', 93),& k3 h* b( [* I/ f% r
        ('陶俊', '语文', 88),- V% P7 M! R2 z0 G" z& B; J- p! s
        ('陶俊', '数学', 84),+ L1 C# V) x+ z
        ('陶俊', '英语', 94),* h1 S/ ]" v& R) \( c* D
        ('刘可', '语文', 80),' |# [* P: T$ u$ m" ^4 L
        ('刘可', '数学', 86),( }/ B5 O6 ?  P3 Z. O: w' w. k
        ('刘可', '英语', 88),5 R4 W" l' G) z. e( l+ n' y: \
        ('李春', '语文', 89),  Q3 |+ t  U* p' J1 v) @
        ('李春', '数学', 80),' m# M- F5 I' f
        ('李春', '英语', 87);
方式一:使用if 复制代码select name as 名字 ,; q  X9 U+ J7 X
sum(if(Subject='语文',Fraction,0)) as 语文,: c" P* W. Z. X
sum(if(Subject='数学',Fraction,0))as 数学, 4 T2 g* V$ \& k6 j; ]
sum(if(Subject='英语',Fraction,0))as 英语,9 l" x" `7 d% M! P& b
round(AVG(Fraction),2) as 平均分,2 Q* c* ^  I! N0 v  f6 ]9 Q* p/ J% I5 z
SUM(Fraction) as 总分' M; ~: W! B/ c
from t_score group by name     & Y4 l& `& X; N$ F, v) h- P1 v; T& e
union( N; Z* p* A9 X1 J4 U* Y& s1 @
select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(: W8 S& Y5 c- r% A! P/ n( e) v
select 'TOTAL' as name,6 F9 C$ i6 D8 |1 g  ]1 S
sum(if(Subject='语文',Fraction,0)) as 语文,( ^! {5 H: {4 O  |% o% s$ p$ S* P3 q
sum(if(Subject='数学',Fraction,0))as 数学,   H# p2 U; n% N
sum(if(Subject='英语',Fraction,0))as 英语,6 j7 y9 c$ C3 K# k1 _! m
SUM(Fraction) as 总分% ~: h1 h$ i2 }) N( X2 j: o$ K9 @
from t_score group by Subject )t
方式二:使用case 复制代码select  name as Name,# ^3 M  M  a! c( H+ s( U
sum(case when Subject = '语文' then Fraction end) as Chinese,$ i2 L/ s2 H/ V: i) O3 g
sum(case when Subject = '数学' then Fraction end) as Math,. L6 o' j! o  I/ v+ f. ?* D# T
sum(case when Subject = '英语' then Fraction end) as English,1 n6 F& e1 X* D, ~
sum(fraction)as score0 @% N: W! w& d0 ?3 P  q
from t_score group by name( ]9 w/ f6 Y, K, r
UNION ALL+ Q: ~. b  v; P3 u) [( {  I
select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(% j/ Y3 c' U; w* ]$ c6 X* _+ o6 L
select 'TOTAL' as name,3 V, ~4 T8 p4 x$ y! ]) K
sum(case when Subject = '语文' then Fraction end) as Chinese,' J+ v) V& s3 W* P5 k( L; u, _$ j
sum(case when Subject = '数学' then Fraction end) as Math,. s) p8 ]9 n. Q% Z- @
sum(case when Subject = '英语' then Fraction end) as English,$ s: G* B5 o( G, ?
sum(fraction)as score3 {$ |3 P2 ?* {' G$ _
from t_score group by Subject)t
方法三: with rollup 复制代码 select ( o/ b3 {4 p% U. y
        ifnull(name,'TOll') name,$ k! w7 ~1 Q7 D, P# j. d+ P! b
        sum(if(Subject='语文',Fraction,0)) as 语文,6 q# f! [( F3 p$ F$ V/ `* {3 ^
       sum(if(Subject='英语',Fraction,0)) as 英语,+ h- |3 v9 g& ^
       sum(if(Subject='数学',Fraction,0))as 数学,! O) N6 A' }! n
       sum(Fraction) 总分1 A$ \/ u0 i: d/ G1 Z
        from t_score group by name with rollup
查询结果如下: ' q: y8 g1 E7 Y: s3 z/ [0 p, x: _8 g; X) V. C
 
 | 
 
x本帖子中包含更多资源您需要 登录 才可以下载或查看,没有帐号?立即注册 
  |