|
|
原始插入数据如下:
) h6 I7 Z- {# C! P" N& H要求查询结果如下 :- r$ O% L9 R x7 m) K
5 }8 n( [8 e/ @1 I8 c
7 G4 `/ K8 L+ \% Y. q" a5 i7 L. E6 E创建数据库、表6 K J' \+ X0 _$ r& k* H$ r9 E
- create database tests;
- T$ l* i2 q0 c5 m) K; P& x - use tests;" e# Q2 x" Z g7 D2 x- p
- create table t_score(
$ {( Q3 \2 k1 S* z# t - id int primary key auto_increment,
4 L* y0 ~, E, C5 K ~4 g, @& v - name varchar(20) not null, #名字8 o9 Y3 l0 g& `( T; F
- Subject varchar(10) not null, #科目
1 D, S t, h, l$ ` - Fraction double default 0 #分数
" |; R7 x+ {+ p: M" z! {+ R5 ` - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES& L6 [: V0 I: V/ I" U! E
- ('王海', '语文', 86),. B% }: {; w! l* b8 U( I3 r: y
- ('王海', '数学', 83),5 g m( K2 Z* t; k* J6 m
- ('王海', '英语', 93),# G( h- j7 R7 y3 F; @- K2 j0 W' J
- ('陶俊', '语文', 88),2 A1 O; m4 N5 w7 U* G! r5 @" q
- ('陶俊', '数学', 84),
) c' C( x! P, D1 K# c - ('陶俊', '英语', 94),/ D5 j+ l. [( T7 b- n& j, d
- ('刘可', '语文', 80),
8 D4 x5 y( h6 ~- c3 L$ f" w - ('刘可', '数学', 86),' x8 ]+ _1 j+ P
- ('刘可', '英语', 88),
8 z/ k4 [5 a( C( y - ('李春', '语文', 89), ?' Y: h. ]3 R
- ('李春', '数学', 80),
$ ~7 ^6 _9 m. |$ M9 \. ?$ V - ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,, A" Y5 i. i0 Z0 ~6 I. @
- sum(if(Subject='语文',Fraction,0)) as 语文, u5 d+ _: {& y% g t2 N; M
- sum(if(Subject='数学',Fraction,0))as 数学, % q( P8 J8 L5 j/ S K9 v4 A
- sum(if(Subject='英语',Fraction,0))as 英语,
& O: r1 z; S7 ~' Y8 R. y6 y - round(AVG(Fraction),2) as 平均分,
( e1 W; m' B# y/ r) N' I; ~% c - SUM(Fraction) as 总分4 B. Q0 T+ C1 p1 x; M9 Y4 F7 ]/ T3 h
- from t_score group by name
* n! U& i$ Z. y* I# F/ e& O - union
% E) {9 W& M* d" T& Q( {2 A - select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(& |+ I. @/ Q3 }8 U! u# {( L
- select 'TOTAL' as name,0 B0 x% j$ Y, x& U8 e' [
- sum(if(Subject='语文',Fraction,0)) as 语文,+ ^9 s# h( r D- O
- sum(if(Subject='数学',Fraction,0))as 数学, b+ a7 j! ]1 d- K# B2 t. v* ]
- sum(if(Subject='英语',Fraction,0))as 英语,
2 P$ Y0 N0 X" Q9 ~$ B/ z - SUM(Fraction) as 总分
8 ?' {* ?3 Z) A" J - from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,' Z6 x" v- s8 T Y k! b
- sum(case when Subject = '语文' then Fraction end) as Chinese, _" K0 o0 v, v/ ]0 b
- sum(case when Subject = '数学' then Fraction end) as Math,
( }+ v) ?+ M: f0 G, t+ U3 N# w - sum(case when Subject = '英语' then Fraction end) as English,& {" R3 Z1 C. d( s2 F
- sum(fraction)as score) p( S# s3 Z; h0 E5 K' f1 G
- from t_score group by name# k9 d; N, m0 }: f0 X
- UNION ALL
4 p( A1 v9 D, z/ R; q! L! D- _ - select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(+ M* j1 |: H0 K" j) n
- select 'TOTAL' as name,4 d; k2 p- ?) @: {: ]' b
- sum(case when Subject = '语文' then Fraction end) as Chinese,2 ~! Z. r# N+ E" L8 N' r
- sum(case when Subject = '数学' then Fraction end) as Math,
4 g8 k D7 I' M- c. g& a - sum(case when Subject = '英语' then Fraction end) as English,
* f3 ~" K4 Q0 ^' s% P - sum(fraction)as score
W' U( M# y( K2 p+ z - from t_score group by Subject)t
复制代码方法三: with rollup - select
% ~ Y0 D. g- P' K# D - ifnull(name,'TOll') name,9 Z+ U( y7 s b
- sum(if(Subject='语文',Fraction,0)) as 语文,( ?; L* [* ~. w7 J6 I2 H
- sum(if(Subject='英语',Fraction,0)) as 英语,; b \' N2 m4 c0 y) P
- sum(if(Subject='数学',Fraction,0))as 数学,, n6 \1 B" N" Y# S$ R8 `4 a
- sum(Fraction) 总分. P2 Q6 j8 i1 Y: t1 f! ?+ U/ W5 Y
- from t_score group by name with rollup
复制代码查询结果如下:
( T* ^3 R1 Z' _) `# b
, Z u2 z+ d: z8 t& K! k# ?6 G) B |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|