|
原始插入数据如下:" s$ d& X1 }4 h% d" U
要求查询结果如下 :
. N# ?! N8 f- g' s( O/ b% X0 W1 }! `- U; G$ C1 N" \4 K
0 q3 p2 I8 ~# s" S; P1 i
创建数据库、表
7 I: ?- o( X1 s4 |% W5 _7 Q" C- create database tests;% P' u: I! s k, v9 ~
- use tests;2 S& {2 h5 H1 T$ P9 }; G. m: J
- create table t_score(
9 i6 m/ m! P! i - id int primary key auto_increment,2 k/ \2 {* H& L& t
- name varchar(20) not null, #名字& A/ z) Z! Z. t u0 H$ v
- Subject varchar(10) not null, #科目
) n8 O2 x) E* ` - Fraction double default 0 #分数
+ \7 O- b: R# N! }1 \6 B2 a - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES
/ |1 y# `* i6 O# c8 K - ('王海', '语文', 86),0 J4 Q/ k& W2 J: V& \8 j
- ('王海', '数学', 83),
4 s' u# l" E1 J) X* A - ('王海', '英语', 93),% I) B7 Q! [( L' q& }7 v; M
- ('陶俊', '语文', 88),/ w( K# ]* Q: y/ i
- ('陶俊', '数学', 84),# C. B" @5 B% b' [
- ('陶俊', '英语', 94),
- Y6 @: b8 d# p& q! \# c - ('刘可', '语文', 80),
& @" _8 u6 y. j2 ^$ r+ B8 o) j6 J - ('刘可', '数学', 86),
7 N0 r% ^8 f- a& _2 I6 s6 m - ('刘可', '英语', 88),% j/ N% o+ h6 ~( j9 C: q
- ('李春', '语文', 89),
+ j2 |1 B! _' H9 M2 `) o - ('李春', '数学', 80),
7 a" l2 }" n6 h! w - ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,+ N7 r) X3 ~8 J0 Q5 ^
- sum(if(Subject='语文',Fraction,0)) as 语文,8 J C5 Q; a. I* o/ I
- sum(if(Subject='数学',Fraction,0))as 数学, ) v% @/ N4 E$ R
- sum(if(Subject='英语',Fraction,0))as 英语,
+ Y& k3 O% b0 Y# J! y - round(AVG(Fraction),2) as 平均分,& k* `* u! |2 S; f! `/ ^, j5 P
- SUM(Fraction) as 总分
+ r0 l# b2 K8 a7 O* { - from t_score group by name
; u; g0 `* x7 B9 G% v1 L0 C. h% K - union
+ f3 Z7 s6 {/ s - select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(2 [& P! l; Y' {1 q
- select 'TOTAL' as name, B7 ]3 }) E% I) g" U, `
- sum(if(Subject='语文',Fraction,0)) as 语文,$ a+ j6 U) O: ~: c$ I
- sum(if(Subject='数学',Fraction,0))as 数学,
E1 x: ?. L1 s' d - sum(if(Subject='英语',Fraction,0))as 英语,
- Z% g$ `2 l7 I; Z - SUM(Fraction) as 总分3 A' ?' b8 s: h
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,$ @0 m7 B% y( K& A) K7 s* d" \
- sum(case when Subject = '语文' then Fraction end) as Chinese, ~; r! z! T/ T! N$ c/ ?) M
- sum(case when Subject = '数学' then Fraction end) as Math,
8 {, H- e3 {8 V& m) o$ K - sum(case when Subject = '英语' then Fraction end) as English,
1 I0 M! k- E, c0 y( m, O - sum(fraction)as score3 t: X; k/ r' X+ j' o; s+ o" V: D
- from t_score group by name
6 V. [- s6 R; J# y: ?" e - UNION ALL1 }0 n5 |1 e: |* z8 d: e
- select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(! U8 J( P4 V* i' L8 ?& P/ V
- select 'TOTAL' as name,
& e" P$ Z. d/ b! ^/ y2 m+ w - sum(case when Subject = '语文' then Fraction end) as Chinese,! y# L& u: K M. \$ k
- sum(case when Subject = '数学' then Fraction end) as Math,
/ c# K: ?7 k8 E' I4 V0 ^! v' Q - sum(case when Subject = '英语' then Fraction end) as English,
/ W, R2 Z& _% ], A - sum(fraction)as score. w- a# y! |2 P7 x& Z% B
- from t_score group by Subject)t
复制代码方法三: with rollup - select + N+ f: m6 K" V- K7 \
- ifnull(name,'TOll') name,3 |5 {7 n' E; E: v. B6 u
- sum(if(Subject='语文',Fraction,0)) as 语文,
- C) N$ ?$ B; T! N% o - sum(if(Subject='英语',Fraction,0)) as 英语,; [* d2 L% c) u( z; |2 ?; U
- sum(if(Subject='数学',Fraction,0))as 数学,
( x' ^: k4 n4 c2 Z9 r" k8 L - sum(Fraction) 总分) X b' T$ ?1 H* t5 o% w0 b; ?
- from t_score group by name with rollup
复制代码查询结果如下: ( r- ?: K5 b* a9 \
' X5 H) x5 l% r0 O; {" w
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|