|
|
原始插入数据如下:4 h3 `! ^$ L* d- d
要求查询结果如下 :8 @- y" ^3 h$ h W: A8 y" v) u
) \- R8 F" L w' U7 t" K
6 h' b* t3 W. f5 Y7 D& W6 q创建数据库、表, K( }4 [/ M! ]: _1 ~. M
- create database tests;
; F# `$ C5 {- g+ Q; I - use tests;
" J9 o9 n8 @3 Z. x" ] - create table t_score(; r2 x3 P' t9 J# ?: _9 N/ e
- id int primary key auto_increment,) n8 R/ E+ p- q$ J# j* {$ n7 W1 _( ]+ _
- name varchar(20) not null, #名字1 _ J" _ s# \, E
- Subject varchar(10) not null, #科目6 v3 b! P+ d& c
- Fraction double default 0 #分数
1 d( X8 P$ A' V0 M& ^ - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES1 A3 m* i& \3 |% e1 [, W" i8 d
- ('王海', '语文', 86),6 ^9 i: O( [) r3 ^
- ('王海', '数学', 83),; S: T! ^. E8 ~ v" I# U
- ('王海', '英语', 93),2 K3 ?3 `6 W1 S A# q
- ('陶俊', '语文', 88),
5 d t F+ l% z - ('陶俊', '数学', 84),
, m; b+ S# }: _3 v" o - ('陶俊', '英语', 94),
4 ` y; l4 {2 I! d) ]( c - ('刘可', '语文', 80),- r \1 z3 }2 _2 [' N& n
- ('刘可', '数学', 86),7 F$ m. m. G7 ^2 X+ Q
- ('刘可', '英语', 88),
+ \0 c9 X& Q' W/ N - ('李春', '语文', 89),- a1 c, S2 A! Y% b% t
- ('李春', '数学', 80),3 x0 u. F/ d8 C) K* f
- ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,3 n K6 k8 m+ @
- sum(if(Subject='语文',Fraction,0)) as 语文,
. v, Z: O6 M- S+ t( o1 O W( y* w - sum(if(Subject='数学',Fraction,0))as 数学,
8 }" h8 F+ A, F) q K! u. v: B% y - sum(if(Subject='英语',Fraction,0))as 英语,0 J1 E0 C# D, Y U
- round(AVG(Fraction),2) as 平均分,
7 V) @9 W4 ?* L, P - SUM(Fraction) as 总分/ A# o" d7 T( u
- from t_score group by name
1 V) R# M( ~# m5 e) O: M4 A - union
0 A3 q: ?9 V9 g; W5 T' r - select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(, c% M/ P+ `& u
- select 'TOTAL' as name,' c/ u3 V% K8 C% R8 G
- sum(if(Subject='语文',Fraction,0)) as 语文,3 |: @6 U0 \4 R. I8 g/ x
- sum(if(Subject='数学',Fraction,0))as 数学,
7 Z- d) s9 F$ D S, Z: _& g; ^% T - sum(if(Subject='英语',Fraction,0))as 英语,
0 v5 ~" P2 e# I$ c a2 V& u - SUM(Fraction) as 总分
. D$ `. A5 i; V. Q$ p% t n2 ` - from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,9 j- l% c# j6 @! }+ A' o# E! h6 K
- sum(case when Subject = '语文' then Fraction end) as Chinese,
2 q, Q, U& L- F% x - sum(case when Subject = '数学' then Fraction end) as Math,0 S# ]5 H$ G2 L* A7 P
- sum(case when Subject = '英语' then Fraction end) as English,6 s, u* P: i. a) X7 f
- sum(fraction)as score2 s& q: L8 ?9 m. E( ]+ S- A! j' H
- from t_score group by name$ F& L; {7 b7 ]& G+ X
- UNION ALL
# b5 T3 L& G$ A$ D8 r: u - select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(0 w5 t* L' U ^5 c4 @) ~( Z5 ~* c; s
- select 'TOTAL' as name,- z& n; h3 Q7 d6 t& R
- sum(case when Subject = '语文' then Fraction end) as Chinese,2 n) l3 x+ o8 t2 n
- sum(case when Subject = '数学' then Fraction end) as Math,8 @: D: k$ e. m$ P
- sum(case when Subject = '英语' then Fraction end) as English,
) H( ]! K! h7 n5 c- J - sum(fraction)as score w7 {$ ? g( M% x: M
- from t_score group by Subject)t
复制代码方法三: with rollup - select
! \ n7 V z0 H: j4 d7 U - ifnull(name,'TOll') name,
+ d7 n4 Q, { Z/ R; S! U' q# O - sum(if(Subject='语文',Fraction,0)) as 语文,
0 Z& e0 w: q/ `" N# t1 _/ W - sum(if(Subject='英语',Fraction,0)) as 英语,, ~# X1 S9 Y5 O! W. C5 i3 ]' d
- sum(if(Subject='数学',Fraction,0))as 数学,
/ G, S4 t2 f0 F - sum(Fraction) 总分
1 ]* ^ |9 o7 y8 Q, O% P( x) D! \) P; G# Y - from t_score group by name with rollup
复制代码查询结果如下:
( L) A1 y' d6 N' L' x1 @; ^4 D8 u8 N+ w2 x+ }( k# B
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|