|
原始插入数据如下:; b9 v5 o' R7 i3 S5 O o
要求查询结果如下 :1 S, a, U$ \# s, O) c
; y" k, B% k: _4 A. p
' u/ l* r8 ?' p4 V. n+ f创建数据库、表0 m* Q' v8 t" [$ p; `
- create database tests;
! g% ~' s1 d, P& ]$ z - use tests;
! G9 U- V, V" `0 e9 e( h - create table t_score(
/ x# K6 i: ? P5 S% t3 f- U - id int primary key auto_increment,
$ d' `2 l2 a7 K+ w: d+ t - name varchar(20) not null, #名字1 w- r9 B: s- C) c6 U
- Subject varchar(10) not null, #科目& S3 k$ Z5 G" W! P# y0 @- x
- Fraction double default 0 #分数
& h' j" L. N- ]2 F/ _ - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES
* s1 X: a& ~( F) a4 @, l* K - ('王海', '语文', 86),
, B1 X* Y+ `3 S9 |- h7 d7 ?6 [" J - ('王海', '数学', 83),
4 K. r$ e- \9 v& }3 _2 _" m - ('王海', '英语', 93),
# o6 J" t( D$ N - ('陶俊', '语文', 88),# l+ C7 g+ B1 K9 O
- ('陶俊', '数学', 84),
4 o/ @/ M8 [9 j; c6 W - ('陶俊', '英语', 94),
0 y2 n, G4 i- }% _; w* c+ Z - ('刘可', '语文', 80),
. P, Z# N' K" C6 J - ('刘可', '数学', 86),
# D4 M- P1 T5 ~6 p" | - ('刘可', '英语', 88),+ P7 [1 I# `! ~& U* J
- ('李春', '语文', 89),
) Y: @9 ^$ W w' A$ f9 [ - ('李春', '数学', 80),: X$ j$ _- Z- ]7 B- y& J
- ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,
( R1 r: L) u" i - sum(if(Subject='语文',Fraction,0)) as 语文,
7 l: ^5 i2 m# y# X - sum(if(Subject='数学',Fraction,0))as 数学, , o$ Z' Y- Q' f. G8 Z
- sum(if(Subject='英语',Fraction,0))as 英语,
" T0 X0 K ]$ u - round(AVG(Fraction),2) as 平均分,
/ r: }+ ^5 u4 I5 t, } - SUM(Fraction) as 总分
7 m' ] F, [3 V: D( v - from t_score group by name
- s) n0 A' ]+ y/ c, k; ? - union% a. r2 ~) z' X
- select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(# J3 m- O7 F8 V. S. s8 {
- select 'TOTAL' as name,, S' p0 b6 B6 f8 r& A
- sum(if(Subject='语文',Fraction,0)) as 语文,2 r: y9 q2 _8 U, r* U
- sum(if(Subject='数学',Fraction,0))as 数学,
' m; B1 S; ]0 ^1 | m' x0 K - sum(if(Subject='英语',Fraction,0))as 英语,
6 F o% w. s3 G/ Z# i - SUM(Fraction) as 总分3 b) b" h# ~5 R: s, M/ [9 \+ S
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,
) o& t8 @( F* _* e - sum(case when Subject = '语文' then Fraction end) as Chinese,
, _( Z3 L3 w. A: ^5 c9 p - sum(case when Subject = '数学' then Fraction end) as Math,
/ L4 ~! p( v0 t0 D0 R e6 \" A - sum(case when Subject = '英语' then Fraction end) as English,
' s' x8 u8 |0 A' k - sum(fraction)as score) W s3 B( h" U% e, x
- from t_score group by name
* c+ Z1 }! F# g$ |0 U: \ - UNION ALL1 E0 Q! N* C: |7 Y M
- select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
2 d' V9 s, G0 ]9 b3 b! x8 c$ F( k - select 'TOTAL' as name,/ j2 ~, }. I$ I/ |( t
- sum(case when Subject = '语文' then Fraction end) as Chinese,
( y& {5 s9 D @9 P - sum(case when Subject = '数学' then Fraction end) as Math,& I1 G3 d8 o o1 F5 ` N" m
- sum(case when Subject = '英语' then Fraction end) as English,6 z' u( _, `. q Z% R$ D
- sum(fraction)as score/ J! U) c* W5 m# f2 q7 ^# ?
- from t_score group by Subject)t
复制代码方法三: with rollup - select
% y! d% V, e- l ^ - ifnull(name,'TOll') name,
- M; b; J# Z+ _/ ~0 t7 q/ \ ^ - sum(if(Subject='语文',Fraction,0)) as 语文,1 N% ~# ~: o3 I4 O( j8 ]0 g- X' {$ m+ T( P
- sum(if(Subject='英语',Fraction,0)) as 英语,/ C2 g- _: e$ p% F Q% _
- sum(if(Subject='数学',Fraction,0))as 数学,
8 O C! t/ _0 N# H - sum(Fraction) 总分
1 @, V, a+ Z" O8 i. R - from t_score group by name with rollup
复制代码查询结果如下: ( l& A9 b' ^; N: p% U
1 d" ?0 v; [% M, }7 x
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|