|
|
原始插入数据如下:
' [. F2 _ s; l! u4 K要求查询结果如下 :
/ c+ \5 g4 {8 N1 S9 S# E- ]
. k% l( y7 f* F G
7 J" Y5 ~' d. ~* C( B% u0 W创建数据库、表
" s- ?) p5 a K% J/ K6 b9 M- create database tests;
6 T9 d' ], ]2 k; p/ Z( I - use tests;+ E- ~; W& n$ N K- k2 C& n; B
- create table t_score(% k/ V U# R& Z7 J
- id int primary key auto_increment,* r- Z& F4 D9 E3 M
- name varchar(20) not null, #名字
" D8 s- [2 e5 q; u - Subject varchar(10) not null, #科目8 B4 o: c5 p) T8 N" n0 D
- Fraction double default 0 #分数/ e% g- C" e4 r" w5 b& ]/ h6 m
- );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES3 E, E; s6 g6 _; h) S$ |. i
- ('王海', '语文', 86),8 e, W9 a m4 i5 {' h
- ('王海', '数学', 83),' o* U- Z8 v3 y7 c. e2 D
- ('王海', '英语', 93),
4 s% F% s$ ] }" Y5 Y5 G" \ - ('陶俊', '语文', 88),3 Q5 P# d# D$ A" z# z# x& E" d
- ('陶俊', '数学', 84),
, ]/ b8 ~9 l' [" Z - ('陶俊', '英语', 94),
1 K" c& f8 n; C# ^/ E - ('刘可', '语文', 80),8 {; F; ?, g& q7 q' C4 \ ]
- ('刘可', '数学', 86),& M! l" b* h: [; n( S
- ('刘可', '英语', 88),
z% s5 }9 N& w* i& h2 J - ('李春', '语文', 89),1 l* _! O- b' H; {# u
- ('李春', '数学', 80),
$ v. U. k: g5 Q) B - ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,% S3 ` i& M, A( [; @, N) h/ r7 p
- sum(if(Subject='语文',Fraction,0)) as 语文,
5 A" n2 E& b0 U J* V, `- m - sum(if(Subject='数学',Fraction,0))as 数学, $ u# I/ u+ W1 }. q; ], o. I
- sum(if(Subject='英语',Fraction,0))as 英语,
/ G l7 Y* G. A. m9 f - round(AVG(Fraction),2) as 平均分,6 |( o, G% b# m1 C/ A
- SUM(Fraction) as 总分$ t2 D% k/ s1 r2 ~7 B5 ^
- from t_score group by name
3 p, v% }' j k: C6 R% P# ] - union
& }! `6 a! c& q7 Z- q6 H - select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(
+ X+ _+ Z3 L) V+ s; K7 j' p& O6 ~ - select 'TOTAL' as name,
* ]8 j t9 ~1 B: I - sum(if(Subject='语文',Fraction,0)) as 语文,' y9 }. P3 q% O7 B3 q) y- g' K
- sum(if(Subject='数学',Fraction,0))as 数学, ( Q6 T' T& U `" W, A- O! J# _# S
- sum(if(Subject='英语',Fraction,0))as 英语,
$ j, n3 D8 N! s. e9 J2 V - SUM(Fraction) as 总分
6 |8 j$ }2 K ]9 t( k' H% M+ b; ~ - from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,9 X, T4 x8 o$ \2 ]8 Q
- sum(case when Subject = '语文' then Fraction end) as Chinese,. D4 N2 s. ]' V6 u, d
- sum(case when Subject = '数学' then Fraction end) as Math,1 t; B# D0 j4 A& [
- sum(case when Subject = '英语' then Fraction end) as English,: y, D3 J6 [/ H1 E7 E" C+ M
- sum(fraction)as score
1 E( k' F6 w: ] - from t_score group by name
* P& s+ H! `0 B3 g - UNION ALL! T! k* ]4 w! ?2 R
- select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(! A. k. ~# ~+ _9 i5 U& Z
- select 'TOTAL' as name,
5 T7 n1 t2 m# u; F - sum(case when Subject = '语文' then Fraction end) as Chinese,3 a7 }: q) ?& y9 a4 I5 X
- sum(case when Subject = '数学' then Fraction end) as Math," v3 I5 h3 Z7 M, h3 T
- sum(case when Subject = '英语' then Fraction end) as English,+ d7 s g7 E7 Y$ o+ t9 ?
- sum(fraction)as score
8 B$ Q6 w! h; n3 L+ P - from t_score group by Subject)t
复制代码方法三: with rollup - select
" p! Q ~. s. x0 W2 r - ifnull(name,'TOll') name,
7 v8 p- }9 o+ p1 z7 f - sum(if(Subject='语文',Fraction,0)) as 语文,. ]; Q" ^6 j8 K. ^9 x$ i
- sum(if(Subject='英语',Fraction,0)) as 英语,
o! {; R4 {$ e; v& N& \5 u - sum(if(Subject='数学',Fraction,0))as 数学,
; U6 ^/ d5 J3 P - sum(Fraction) 总分
- `( a8 W8 x/ U' b, H6 Q - from t_score group by name with rollup
复制代码查询结果如下:
5 G2 k) V; u& H4 I5 J* y. \6 m4 p3 H+ b; g
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|