|
|
原始插入数据如下:
0 A- p9 R u6 V9 Y. W2 n要求查询结果如下 :/ C2 q7 `1 F0 J8 Y) I
6 |: P$ ^0 Z( ]3 _8 K
9 }( ]0 c% Q- r' q0 ?# u1 x
创建数据库、表9 O/ Z6 ^" p1 U) ]( m" X. q
- create database tests;; _* [! _5 Q$ l0 T( f
- use tests;
# B' s6 L. Y8 g. A - create table t_score() T) k _7 | L% X/ A
- id int primary key auto_increment,
7 A5 t* } s& K/ | m - name varchar(20) not null, #名字
* S. b9 p6 B. K# s - Subject varchar(10) not null, #科目
" s7 C6 c" a: b* j0 y; r - Fraction double default 0 #分数6 {0 V# {1 K- |- L2 @, e
- );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES
; N, f9 N. v6 ~, i - ('王海', '语文', 86), P( ~3 P; H. ~: |: M3 ^ t
- ('王海', '数学', 83),; ~0 [4 S M$ }
- ('王海', '英语', 93),6 z! J2 Q7 f; E4 Y) \
- ('陶俊', '语文', 88),. T4 G9 f% p) O
- ('陶俊', '数学', 84),
2 {( g/ h! h' h: z& A - ('陶俊', '英语', 94),
9 q( m8 A5 T6 [9 J3 n, k8 q - ('刘可', '语文', 80),
2 q3 w: s0 D O: P! Y* }" |! J - ('刘可', '数学', 86),
6 a) T: @4 V3 X' U7 } - ('刘可', '英语', 88),- ~% \5 p+ K# b& T6 w! |& V3 J6 N+ S
- ('李春', '语文', 89),
0 ^1 g2 Q# |2 A# c- f - ('李春', '数学', 80),* i9 [( \6 a. N ^7 e8 F
- ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,% o0 Y3 |3 `; n& v+ l
- sum(if(Subject='语文',Fraction,0)) as 语文,
& O; Z: I; Q' {% G - sum(if(Subject='数学',Fraction,0))as 数学, C- \- g; {% X. c1 c
- sum(if(Subject='英语',Fraction,0))as 英语,( \$ U# i3 ]5 o6 l
- round(AVG(Fraction),2) as 平均分,5 C0 {4 `, J* U4 `/ D1 M
- SUM(Fraction) as 总分
6 z& J# R* g, n, f6 [ - from t_score group by name # H3 R8 a" [; c0 z6 z
- union, A6 U1 `; [/ i& _2 X
- select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(
! v) }0 ~; Y; q* T# ^ - select 'TOTAL' as name,) I; m$ K9 h6 X% B: s; z4 G
- sum(if(Subject='语文',Fraction,0)) as 语文,
+ b; C) J* f# b8 c' Z - sum(if(Subject='数学',Fraction,0))as 数学,
: b6 P& o9 h' ?; T% R) n - sum(if(Subject='英语',Fraction,0))as 英语,+ @/ f- R0 V% b; V
- SUM(Fraction) as 总分
1 N2 Y2 f7 s% S' ]" m) K1 e - from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,! K; K3 U: k. m, d/ L
- sum(case when Subject = '语文' then Fraction end) as Chinese,
1 N" T# V& i' L) t' ]7 o - sum(case when Subject = '数学' then Fraction end) as Math,4 S5 [& m- o+ c
- sum(case when Subject = '英语' then Fraction end) as English,: f. u- j1 _4 X C; O; m8 B
- sum(fraction)as score
5 d. r7 c' m+ {* M/ g' P - from t_score group by name
/ M- K( X- s& u% y- d1 y4 | - UNION ALL
H+ P, D8 }4 Y7 y - select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(* c! @% i0 `! b' ?9 h8 _4 w
- select 'TOTAL' as name,7 ]8 g: \% a+ C: ~; @
- sum(case when Subject = '语文' then Fraction end) as Chinese,. ]; |% u4 X. a
- sum(case when Subject = '数学' then Fraction end) as Math,
( G4 a4 o. h- Z6 v) @0 ~; D- s - sum(case when Subject = '英语' then Fraction end) as English,
) z& c6 A! i! O" a& p - sum(fraction)as score
4 ^. G. q' @, [3 }! O8 s' ~ - from t_score group by Subject)t
复制代码方法三: with rollup - select
5 _! d/ r# P9 k5 r! i2 e6 a9 b, A - ifnull(name,'TOll') name,
- [- s: G$ P1 d. B# E - sum(if(Subject='语文',Fraction,0)) as 语文,
- F' ~/ Z4 ~ W/ A - sum(if(Subject='英语',Fraction,0)) as 英语,
0 N+ i: S2 E+ d6 [% G: H, w - sum(if(Subject='数学',Fraction,0))as 数学,) e; n9 c' |, h- A( G
- sum(Fraction) 总分
: H! N1 @) `0 i7 O7 R( G! \+ B - from t_score group by name with rollup
复制代码查询结果如下:
! ?3 }) i% f5 g3 g: S9 E' |0 y0 j2 A2 O6 E6 ]) c
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|