|
|
原始插入数据如下:6 z* M5 d% o N) D8 x4 P. \
要求查询结果如下 : M; H$ _$ m4 K
$ r% |- I7 n/ g" V+ {, p9 N* G/ N5 t8 R. t5 }7 l) _, P# o
创建数据库、表
* T% v9 M% G5 z; j* I- create database tests;! u7 f' R# d8 X0 ?. c( h' M
- use tests;3 x/ H2 X( v/ R( O7 W, o
- create table t_score(
( h3 O8 y7 B ]0 j - id int primary key auto_increment,
7 q- ~, m* Z O' \2 f" N8 H - name varchar(20) not null, #名字
4 r- r4 K6 P6 h: g; P- p* i) F - Subject varchar(10) not null, #科目
0 i8 T( b& r9 D3 n3 f - Fraction double default 0 #分数
2 W }: @; {+ l3 ~& k - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES
2 G6 H, \6 R3 o) Y. v - ('王海', '语文', 86),2 q9 X1 Z. e$ p: ?8 r3 X- }# L \
- ('王海', '数学', 83),
6 t! Q: D7 k8 j' \ - ('王海', '英语', 93),
; j" k$ e8 D4 m' z - ('陶俊', '语文', 88),
0 g5 }" V2 K. ~2 C# ~. `% P - ('陶俊', '数学', 84),
6 m! n6 D' |6 C2 d - ('陶俊', '英语', 94),( \* r* w$ B1 f9 Y' ]( t
- ('刘可', '语文', 80),7 k. N, P4 B9 g1 ?% L
- ('刘可', '数学', 86),' y2 ]% g, {* h2 E" P& U
- ('刘可', '英语', 88),3 w# k9 k- J, }$ ?
- ('李春', '语文', 89),9 K8 ~, O0 d$ _; z9 ~& E
- ('李春', '数学', 80),
8 ?% u& [3 O5 b1 T - ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,, e: M! l0 j* L* N' p% b! h
- sum(if(Subject='语文',Fraction,0)) as 语文,/ \) A2 m" j" u+ O" A
- sum(if(Subject='数学',Fraction,0))as 数学, 6 _( W, R. e/ h- U" N" R
- sum(if(Subject='英语',Fraction,0))as 英语,$ D. k3 r2 v! T( ^
- round(AVG(Fraction),2) as 平均分,0 f% C4 d: J% y. r- I. f' D
- SUM(Fraction) as 总分
' S: ]3 m U2 L/ S+ m* g - from t_score group by name
: c Q5 f- v* i0 x - union7 {& H# |3 u5 w; B, R5 S+ T# V% [
- select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(
" G7 l$ j* H, X; V8 |: z - select 'TOTAL' as name," S9 E$ U& V! [4 T0 G9 R* i
- sum(if(Subject='语文',Fraction,0)) as 语文,7 D8 V9 ]9 d) m1 S& P
- sum(if(Subject='数学',Fraction,0))as 数学,
( C) O9 Z) g8 \+ ~( W; ]* }2 R - sum(if(Subject='英语',Fraction,0))as 英语,) }) A' J) L/ a& g! O
- SUM(Fraction) as 总分' L& U1 L3 i( `- b/ `1 W4 d/ D) _0 `
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,
) s/ i3 [0 d* H& G; V5 |& G - sum(case when Subject = '语文' then Fraction end) as Chinese,
# c' r5 j( @2 H - sum(case when Subject = '数学' then Fraction end) as Math,
! W1 G l" T' T1 R. R6 t1 L) r# ^ - sum(case when Subject = '英语' then Fraction end) as English,- m/ K6 g) g$ { d5 }" b8 E. Q
- sum(fraction)as score
9 {$ v: O& h# u5 ]" F - from t_score group by name
3 U# k* W4 g# Y5 i7 [, t - UNION ALL
; Y% m! ? L% c - select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
% ]% R; f/ Q/ W1 i2 \3 a - select 'TOTAL' as name,
: p2 j- D, l) r7 M. X) _% ] - sum(case when Subject = '语文' then Fraction end) as Chinese,. \5 x! [, b2 n
- sum(case when Subject = '数学' then Fraction end) as Math,7 A1 v9 g0 N0 X( R* f5 D. p Q
- sum(case when Subject = '英语' then Fraction end) as English,9 C* {& Q- L j- P/ K% @# c
- sum(fraction)as score# L& _ _: f, ^. Z6 V" T
- from t_score group by Subject)t
复制代码方法三: with rollup - select
5 B0 c2 }8 `: X& K) q. U; ^ - ifnull(name,'TOll') name,
6 m! i3 r% g) j - sum(if(Subject='语文',Fraction,0)) as 语文,
# B* x* [* T! Y" } - sum(if(Subject='英语',Fraction,0)) as 英语,+ }" L& G+ Z, V* r: G2 u
- sum(if(Subject='数学',Fraction,0))as 数学,
5 B# G0 k7 b8 p' x: H8 e# z - sum(Fraction) 总分
7 g0 n7 O# }/ w - from t_score group by name with rollup
复制代码查询结果如下: 2 B1 H- T9 r8 ?% E% q" H
0 d7 h! ?# L: X/ o3 t# K9 e, D5 G |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|