|
|
原始插入数据如下:
) w( \/ R3 k+ w( k, a5 i4 `要求查询结果如下 :2 Q/ l. q+ ^( S1 {: t9 a. V9 ?2 F
) e7 n4 |" [; ~5 V
1 }# B0 }9 v, ]0 {创建数据库、表
6 F. F# v2 p: }( I- create database tests;
4 y7 [- @! o9 g. V% g - use tests;/ V- M3 v8 F! I1 X: X* X6 L
- create table t_score(
2 } @/ t4 z: e: P4 g - id int primary key auto_increment,
6 |1 c, C7 ^& p& N. j - name varchar(20) not null, #名字* h8 F+ A' y$ \
- Subject varchar(10) not null, #科目
" X) q1 F0 f, } - Fraction double default 0 #分数9 T( y3 n" { K4 q) Q
- );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES' U: J4 ]1 t/ K2 g4 F
- ('王海', '语文', 86),, b( o- l5 k* \
- ('王海', '数学', 83), c: U! |3 S9 O0 I" A
- ('王海', '英语', 93),
4 R8 i2 L0 r+ M1 A - ('陶俊', '语文', 88),& N7 @* Y6 t# V/ |0 D
- ('陶俊', '数学', 84),) z# m, e$ r1 A2 L/ H$ m
- ('陶俊', '英语', 94),
5 |3 ~5 {+ S: g% e& `: L. ~ - ('刘可', '语文', 80),
; v" l# |7 [4 z" [3 d - ('刘可', '数学', 86),* |( ^* v, A8 K+ x9 P+ U2 Y& e
- ('刘可', '英语', 88),
9 x. V) g0 X) K! Q! \- ? - ('李春', '语文', 89),
9 D. a9 O$ O$ {* [, k/ F7 Q - ('李春', '数学', 80),6 w1 H" f9 m% d# ~2 n
- ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,
6 n0 L. k# ?3 ~ - sum(if(Subject='语文',Fraction,0)) as 语文,
! H& k. n: f$ M( a5 ?% S/ Y; v - sum(if(Subject='数学',Fraction,0))as 数学,
8 F, n7 Y" }8 k# ~" w3 a. r2 a7 l - sum(if(Subject='英语',Fraction,0))as 英语,+ W5 x7 V8 b& a. F& M0 J
- round(AVG(Fraction),2) as 平均分,/ S5 ` x2 s+ ]) f6 P, v" u
- SUM(Fraction) as 总分/ e* r$ J$ Q4 r9 t& P9 [0 o
- from t_score group by name ! r( B" k6 _6 B" g, p+ p
- union
# H" ]$ @, y$ B- e - select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(7 r5 Q3 C$ |9 G9 l. @3 |( _6 W& y
- select 'TOTAL' as name,
- u3 N& D9 g% U; w - sum(if(Subject='语文',Fraction,0)) as 语文,8 }! a5 \9 S6 A4 [) i5 d4 e9 }" f
- sum(if(Subject='数学',Fraction,0))as 数学, 7 x" a; `/ h; {- r( { v& Y
- sum(if(Subject='英语',Fraction,0))as 英语,$ Q/ N7 O5 _. j, J
- SUM(Fraction) as 总分* \2 r8 g1 x% f2 Z) q6 R9 J2 E; @
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,
; w+ ^3 ~' ~# y2 z$ i - sum(case when Subject = '语文' then Fraction end) as Chinese,$ r2 C5 S- _( L+ _ V- y
- sum(case when Subject = '数学' then Fraction end) as Math,2 H5 b# b- O1 z" g4 q/ W" G
- sum(case when Subject = '英语' then Fraction end) as English,
6 M' ?3 h# N$ C: g& `" `! Q* W) e - sum(fraction)as score
9 a' G* @; ~5 o ? W# B0 K: A - from t_score group by name
( M- H" @3 o6 A - UNION ALL3 [, U* D; s$ Y
- select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
4 S# I7 V# Y c3 p% T3 V - select 'TOTAL' as name,
! `- N) m+ o5 n7 X" j - sum(case when Subject = '语文' then Fraction end) as Chinese,. J: Q6 n$ T9 I5 ^8 s9 N! U, Q
- sum(case when Subject = '数学' then Fraction end) as Math,
: b! e& H4 A( K% I: k - sum(case when Subject = '英语' then Fraction end) as English,$ T9 Q2 B. d; h/ B+ h
- sum(fraction)as score
, `1 Y- ^. e; y/ A ] - from t_score group by Subject)t
复制代码方法三: with rollup - select
$ Q0 @: T, l% G& o2 I - ifnull(name,'TOll') name,( S6 T4 w4 t1 i2 O5 }
- sum(if(Subject='语文',Fraction,0)) as 语文,8 y5 U$ G0 z1 P/ {
- sum(if(Subject='英语',Fraction,0)) as 英语,
p) o# E% {8 r, P* J" @/ {/ n- L2 u - sum(if(Subject='数学',Fraction,0))as 数学,
! Z L% p; r9 j0 S& L/ m - sum(Fraction) 总分8 I! p. Z4 B) y7 y
- from t_score group by name with rollup
复制代码查询结果如下: 2 D m: F+ e( t5 s+ H7 z
+ X$ R# ^2 Z& d- ~. R
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|