|
原始插入数据如下:
5 B: Q2 m8 B, I* J, L1 ^要求查询结果如下 :
: m& i4 I. W9 {. `) R5 H+ [0 Q1 g9 t' a; H1 V
; k8 O: O' S' p! E创建数据库、表
& N; _) M$ T" i2 u2 ~' W) `- create database tests;
( V: Z, F" }3 w- f7 {# C2 W - use tests;
# v# g! j3 P+ P' o: A: e - create table t_score(. G. m4 P' c- E( I5 q( |3 g
- id int primary key auto_increment,
9 f4 E/ V3 y3 l% ]; Z. f - name varchar(20) not null, #名字" }% S8 \( M# \- v" K! L1 }
- Subject varchar(10) not null, #科目
5 k/ a' g; x1 t' u4 N9 V: G! W4 k - Fraction double default 0 #分数
* e! X/ |) s2 f% H% C+ l+ U! G. H - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES
* U) ~ F( ^- e1 u* K - ('王海', '语文', 86),
6 D! K) q" B/ @5 e6 { - ('王海', '数学', 83),
6 G- k" V1 r2 E; C - ('王海', '英语', 93),
+ t4 F9 _; @, \7 q - ('陶俊', '语文', 88),
! {% p" a' d, U6 M - ('陶俊', '数学', 84),
5 M' i& A- `8 w& C; [ - ('陶俊', '英语', 94),
2 s# S* x7 E/ A" m8 N- M4 D5 t - ('刘可', '语文', 80),; g- s6 i) f! i0 ?
- ('刘可', '数学', 86),8 C+ F* e2 A0 N4 ^$ C- y
- ('刘可', '英语', 88),
' c$ L- j u7 ?8 q - ('李春', '语文', 89),7 S2 M) m. V( T/ G5 I' r- M
- ('李春', '数学', 80),
. J" _+ f5 P% @7 d - ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,& s4 a O: I/ b
- sum(if(Subject='语文',Fraction,0)) as 语文,
# _! B# _/ L9 Z9 ] p - sum(if(Subject='数学',Fraction,0))as 数学,
1 I% K2 I6 t) ~$ U/ r9 z3 q0 [ - sum(if(Subject='英语',Fraction,0))as 英语,
5 N$ G6 F! U8 S2 _0 W7 R" h1 J - round(AVG(Fraction),2) as 平均分,: K5 N$ n! u- e* X7 H+ U% [
- SUM(Fraction) as 总分
9 M/ N/ z) O6 l+ z" v - from t_score group by name $ e" ]3 x/ Q8 }6 O) \) J, V' d- i
- union
1 I/ Q% J5 A* _. t( X. u8 e6 Q' L - select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(
! m0 X3 A) A& W7 n5 Q- O - select 'TOTAL' as name,
- S! r3 G% Q3 h+ ?% \ - sum(if(Subject='语文',Fraction,0)) as 语文,
0 u( p) b3 S3 T, y1 g - sum(if(Subject='数学',Fraction,0))as 数学, - `9 F: }5 N( M
- sum(if(Subject='英语',Fraction,0))as 英语,2 R- l2 s' C; Q6 P1 J. N
- SUM(Fraction) as 总分) s4 }( B' O8 I/ A0 h# L) _2 J
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,
0 u* F2 O; {( s: T: l1 ], q+ i0 x - sum(case when Subject = '语文' then Fraction end) as Chinese,
3 |' Z0 [" g# P2 I1 _6 f - sum(case when Subject = '数学' then Fraction end) as Math,: a6 E3 Y1 n* c
- sum(case when Subject = '英语' then Fraction end) as English,. d$ V3 F. [2 B! O
- sum(fraction)as score3 w( x% n- [! g0 c6 l/ U3 |
- from t_score group by name3 Y( |& ~) x! x1 `5 Y7 R
- UNION ALL V5 V. Q8 x4 B1 Y7 y
- select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(4 b! C# z2 \4 t% s
- select 'TOTAL' as name,# _4 ]% d! h5 u$ c
- sum(case when Subject = '语文' then Fraction end) as Chinese,! z" g8 \% [+ g" \
- sum(case when Subject = '数学' then Fraction end) as Math,9 ~! N! D2 z6 u' e, d! {
- sum(case when Subject = '英语' then Fraction end) as English,
2 C& o, D3 ^: J$ T. I; d+ Y4 d$ I( ` - sum(fraction)as score7 u, K3 L' z. ~& f
- from t_score group by Subject)t
复制代码方法三: with rollup - select
) M2 _2 |/ p: O( L; M& T5 Z - ifnull(name,'TOll') name,
, E6 _; r* w, @; l- f8 E - sum(if(Subject='语文',Fraction,0)) as 语文,
/ F: Q8 i* G' N) _) P - sum(if(Subject='英语',Fraction,0)) as 英语,
9 ^) T& w; y; X- Y+ o: X - sum(if(Subject='数学',Fraction,0))as 数学,& |8 m+ y5 W* i# d/ C- q
- sum(Fraction) 总分5 i1 W$ `0 I- ^" B$ q* e) |+ S
- from t_score group by name with rollup
复制代码查询结果如下: ; ?. s% n& a: `4 y) o
; i& b* L S2 H1 _" B4 d |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|