|
原始插入数据如下:% d. @: r1 |7 T4 b9 |# G6 o6 s; \, G
要求查询结果如下 :
4 q9 K m8 M+ P z6 ]; ~) m5 G/ A& W) j9 \* d! f
& n: l# q7 k6 k2 l创建数据库、表
! m4 _0 F& e m" C' B K- create database tests;
4 L+ L& R3 H; H6 C' |7 S - use tests;9 x! y7 `4 \0 o2 q! ]- P
- create table t_score(
; F* t# j( Z. D, j- Y% u7 h2 I - id int primary key auto_increment,6 K, @' L0 j3 {
- name varchar(20) not null, #名字0 m2 R' G6 C# h' @3 t) m; ]; C* q
- Subject varchar(10) not null, #科目6 h; V3 N3 i8 u# r, h# X
- Fraction double default 0 #分数
5 `9 y+ ?) Z$ x1 o2 ~: \ - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES5 M/ c, x! M, @9 L& a
- ('王海', '语文', 86),
; n" Y# a" _6 R8 [# ?$ r; r - ('王海', '数学', 83),
+ D* ^) }0 T* ~5 L. s; M9 W) K - ('王海', '英语', 93),
) B+ O' w' S' f/ `4 B - ('陶俊', '语文', 88),
: R+ a' _; M. P4 f - ('陶俊', '数学', 84),9 D+ X8 B, }- Y6 H! ^( M
- ('陶俊', '英语', 94),
6 D0 w2 p8 ]% ]& f( u - ('刘可', '语文', 80),
5 R8 {3 G" @9 t' h, z6 M( ^* b4 f7 G5 V - ('刘可', '数学', 86),
1 s2 f, Y, R2 m Y W5 d - ('刘可', '英语', 88),; V$ J5 V4 m& x* o' S
- ('李春', '语文', 89),
% ~4 L/ e8 w6 r/ y% O9 }6 w# q - ('李春', '数学', 80),
! |$ K- I: M& v" F+ M; P, o$ c - ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,
* ~" C5 A/ ?0 C3 R4 \0 m6 S$ y- x - sum(if(Subject='语文',Fraction,0)) as 语文,
, O: i+ n5 \: w* l- X4 ~ - sum(if(Subject='数学',Fraction,0))as 数学, 3 \+ ~5 N* N$ X% d5 {2 e+ R8 |
- sum(if(Subject='英语',Fraction,0))as 英语,
( ]& x ?& F w - round(AVG(Fraction),2) as 平均分,
0 H+ O5 r+ V( b1 U# L - SUM(Fraction) as 总分
; ~! K Q* ?1 n - from t_score group by name
. e% n8 i, m$ C" `- X$ ~. ^ - union& r, G/ h9 d* H k4 y$ v9 c
- select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(
- d9 t" \$ e* z% s - select 'TOTAL' as name,
! e7 y& y* j/ ?* P$ o) h - sum(if(Subject='语文',Fraction,0)) as 语文,( X$ b8 H+ K9 a7 j
- sum(if(Subject='数学',Fraction,0))as 数学, 9 _/ t" \! `, y' b E n
- sum(if(Subject='英语',Fraction,0))as 英语,
$ [3 ]9 }+ |7 g" }, J( C' v - SUM(Fraction) as 总分
$ _: M; h, H" y0 c - from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,! Y& H: Y. p. T, Z- O+ e9 g
- sum(case when Subject = '语文' then Fraction end) as Chinese,$ ?" X% Z) ~8 p# |
- sum(case when Subject = '数学' then Fraction end) as Math,+ i9 `5 T3 ` I- |0 z5 Y! h
- sum(case when Subject = '英语' then Fraction end) as English,
& g% B8 v4 D0 L4 ?' d. w - sum(fraction)as score k6 m. d* |% d, t, r. p
- from t_score group by name
2 ~( R% e. n1 F8 C/ w - UNION ALL( B) Y. a9 \6 c/ w) T# \
- select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(3 R8 P6 c* R F1 b8 @
- select 'TOTAL' as name,
# Q8 }3 K8 j' C; f6 |2 i - sum(case when Subject = '语文' then Fraction end) as Chinese,) a2 n+ t) v S
- sum(case when Subject = '数学' then Fraction end) as Math,
# x3 m) @# d( X* r' V" p$ M2 a* N - sum(case when Subject = '英语' then Fraction end) as English,
( o+ o1 W. |( D8 _' q; d6 y# R - sum(fraction)as score
$ E2 H5 @* u/ O6 \) M - from t_score group by Subject)t
复制代码方法三: with rollup - select
# }3 U1 X0 W$ n+ C( K - ifnull(name,'TOll') name,6 A5 S! f4 T# H6 \# q9 \
- sum(if(Subject='语文',Fraction,0)) as 语文,- R1 N8 \7 I1 i ^, C, ^7 T0 Z
- sum(if(Subject='英语',Fraction,0)) as 英语,
) L7 R7 A/ e% [$ U" I9 J( c - sum(if(Subject='数学',Fraction,0))as 数学,
2 H% [: v1 V: i) [& l* O# L - sum(Fraction) 总分
/ }$ w0 s/ r2 _' q: K# y2 @ - from t_score group by name with rollup
复制代码查询结果如下:
& H, y9 M; K( D. t
$ y5 a. w' h8 }! ]4 K( s! P |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|