|
原始插入数据如下:9 Z4 J- T+ R' W2 `3 X/ w
要求查询结果如下 :# J$ U" Z7 v0 W7 {
# q7 ]1 i& V; ]; a6 }
# S) P' |) o' J' g4 K+ e. T: i创建数据库、表$ w! } D a+ Z5 K) ^( K
- create database tests;
8 f" E7 J3 b8 L: {5 W( M - use tests;% M; Q/ \. K B0 L# y( i
- create table t_score(( U: g3 Y8 g' D
- id int primary key auto_increment,
0 `" n' f3 o# a/ R3 M& Y& P - name varchar(20) not null, #名字
4 h" z3 |$ Y7 @5 z8 T. k - Subject varchar(10) not null, #科目
2 e! x2 Q7 {2 [* K1 p - Fraction double default 0 #分数
5 O' }9 H6 H! ~ - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES Z( Z4 N4 a# T- u- j a1 _, A
- ('王海', '语文', 86),
. {3 |4 T$ B' y1 b2 ?' b - ('王海', '数学', 83),
2 |$ X9 R5 \2 Q2 t. X5 Z - ('王海', '英语', 93),$ ~5 R* {# i- \+ b) b, g0 ?1 c. O
- ('陶俊', '语文', 88),
# q: W) s( v+ C2 v3 j - ('陶俊', '数学', 84),
2 J/ n: x0 p( ]3 S" k - ('陶俊', '英语', 94),6 P: c6 |4 M2 e- N; I" N" b8 J+ N) P
- ('刘可', '语文', 80),
, _3 o4 ]6 v' U# E9 _6 p1 B - ('刘可', '数学', 86),* ?9 T- d# _8 b7 |; A3 o6 L/ a, y
- ('刘可', '英语', 88),
& n6 i# S! i. M' v - ('李春', '语文', 89),
2 t5 U+ w+ Z0 o8 o - ('李春', '数学', 80),) c5 x4 q8 y& C0 y9 v
- ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,
+ x0 S; }. m/ t1 Q! j8 W - sum(if(Subject='语文',Fraction,0)) as 语文,
0 q! C7 k' E0 Y' [( s1 f; A - sum(if(Subject='数学',Fraction,0))as 数学, , @: ^, W" x: t. ^2 A
- sum(if(Subject='英语',Fraction,0))as 英语,& y& O3 M' ?" V4 ^0 j: S9 X
- round(AVG(Fraction),2) as 平均分,2 W; G+ Q: L! x
- SUM(Fraction) as 总分! J' v' d4 _: z) ~) I
- from t_score group by name
8 w7 |3 k |' a7 i2 s+ U - union) e9 Z; M9 S! {1 F) h6 |) S
- select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(% s1 C0 A! I6 W' r( p# m
- select 'TOTAL' as name,
! j! S- N3 D- i) Z+ J( i - sum(if(Subject='语文',Fraction,0)) as 语文, b; }- O% F, Y
- sum(if(Subject='数学',Fraction,0))as 数学, ! M4 e6 j$ a7 v& C# b/ R
- sum(if(Subject='英语',Fraction,0))as 英语,
- N6 A& ]/ b6 Y2 g4 C6 m. l - SUM(Fraction) as 总分2 e2 y' ^1 V$ F8 {. |2 i
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,5 N7 V' C0 r5 W- J
- sum(case when Subject = '语文' then Fraction end) as Chinese,
5 c, C7 G2 |8 N+ P - sum(case when Subject = '数学' then Fraction end) as Math,
+ a# }7 K3 S, {3 O - sum(case when Subject = '英语' then Fraction end) as English,
B5 f0 n! x; ?* i F! Q# e - sum(fraction)as score
; i1 t8 [7 u: a - from t_score group by name) `: [" {* a2 g: S2 ]9 J4 j6 R0 d9 Q
- UNION ALL6 G' J9 T8 _- @' Z3 X1 u, {) [, X( P. X
- select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(5 |0 ]8 X; h; N7 l
- select 'TOTAL' as name,
' ?- E! N8 `' I. z - sum(case when Subject = '语文' then Fraction end) as Chinese,7 J0 u, K6 A3 d9 z
- sum(case when Subject = '数学' then Fraction end) as Math,: n- }. E9 A# s; n$ P
- sum(case when Subject = '英语' then Fraction end) as English,
: D' I S" e! u2 t: l4 u6 R - sum(fraction)as score+ d9 c8 ^2 e/ L8 z2 b; y" p+ g
- from t_score group by Subject)t
复制代码方法三: with rollup - select
4 N4 c$ B- b' ?. B( d - ifnull(name,'TOll') name,5 v$ O4 G1 ^4 P% Y* Z
- sum(if(Subject='语文',Fraction,0)) as 语文,
! u- N8 b/ g. v3 R9 K( H4 r - sum(if(Subject='英语',Fraction,0)) as 英语,
; _( q# I% x* N/ C- I2 h - sum(if(Subject='数学',Fraction,0))as 数学,( N: _$ Y% L4 u. v% j) B
- sum(Fraction) 总分
. \; C+ {7 G& S1 r - from t_score group by name with rollup
复制代码查询结果如下: 2 q$ |( o9 |1 F' `9 p s& P
) t; G q4 F1 ]0 T: S3 ` |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|