|
原始插入数据如下:
7 f5 @; q, c' Y% h5 J' L要求查询结果如下 :4 a% |+ l2 d' t: k
9 a$ i# v) p- g
, Z" s7 M- ^+ R; x* g$ ?3 F创建数据库、表! h' ~3 _/ H% n: Z# ^
- create database tests;9 h7 u1 v0 j2 o, S4 W0 o B9 j$ i
- use tests;
^% Q8 x( ~3 N6 h1 l" w$ O, h5 { - create table t_score(7 K2 b ]/ m7 ~6 m
- id int primary key auto_increment,) r# a) m/ J+ K6 O7 `# k
- name varchar(20) not null, #名字
$ @. _2 o6 E8 R& U - Subject varchar(10) not null, #科目
( r/ f. z7 F( D& R! [ - Fraction double default 0 #分数
4 B9 V5 j, H) C - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES' M: S6 Y0 X& B% U3 C, o* S! V
- ('王海', '语文', 86), Q8 {( z8 c P( @
- ('王海', '数学', 83),. I* B" f8 q, A6 \* y" h
- ('王海', '英语', 93),: W5 k# ~9 i9 l# h8 N
- ('陶俊', '语文', 88),2 R' J& b3 L" r# j
- ('陶俊', '数学', 84),' ], |# g6 l2 i9 M+ q" z1 R
- ('陶俊', '英语', 94),7 x. I- \ K" d3 A3 @1 G
- ('刘可', '语文', 80),
. N, z8 s: \0 }1 e3 X* v' f$ R - ('刘可', '数学', 86),
" I. H2 ~4 E- W6 @( p/ {2 y - ('刘可', '英语', 88),# y. m1 o( i! J% l: E
- ('李春', '语文', 89),# Z( G/ D* r% H2 {4 m% J# P
- ('李春', '数学', 80),
* p/ J2 S: |* Y- Y6 R - ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,( Z/ j; b, d# w S$ H" ?: S3 l$ v
- sum(if(Subject='语文',Fraction,0)) as 语文,( n$ {; \" b7 W! x7 R
- sum(if(Subject='数学',Fraction,0))as 数学,
8 a- G/ j0 Q& r. }7 Q1 K8 U - sum(if(Subject='英语',Fraction,0))as 英语,
x& g+ Q9 \. D1 J5 Q - round(AVG(Fraction),2) as 平均分,
. ?7 `3 X; K6 L2 i3 D% @ - SUM(Fraction) as 总分
Q1 Q+ _; `" X1 W - from t_score group by name
; }- V) Q, G: Q; r8 A - union
3 I; B/ C- K+ ~" T* F% c - select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(% z8 p% H4 Y* G5 ^
- select 'TOTAL' as name,
0 l6 m. J6 v7 z- C$ B$ c - sum(if(Subject='语文',Fraction,0)) as 语文,
) p/ ~1 k: |0 ^' d* M8 Z - sum(if(Subject='数学',Fraction,0))as 数学, + ?; X8 `* r( F
- sum(if(Subject='英语',Fraction,0))as 英语,( m7 D! D/ `/ R/ _7 o+ c
- SUM(Fraction) as 总分7 A: ]$ k5 E- y. u" H% @ e4 x5 r
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,& f& M2 H4 K* ^) ?# J
- sum(case when Subject = '语文' then Fraction end) as Chinese,4 S2 F, Q* }' y5 y
- sum(case when Subject = '数学' then Fraction end) as Math,
! L5 x: }3 S- Q8 {% f - sum(case when Subject = '英语' then Fraction end) as English,/ f/ g. j$ I: \: J( w$ ~
- sum(fraction)as score+ G) R6 Y* g; f% R
- from t_score group by name/ I* }; n1 i6 J2 X/ k5 \
- UNION ALL
( a7 a! _9 @& G - select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
' {! A+ z, @: C7 ~, H7 D# v2 b - select 'TOTAL' as name,7 ?2 S! a2 W% e. V
- sum(case when Subject = '语文' then Fraction end) as Chinese,2 x4 ]5 p, Q& s. {" Y7 m
- sum(case when Subject = '数学' then Fraction end) as Math,
! X+ q4 k! n" W8 |) k% u( ` - sum(case when Subject = '英语' then Fraction end) as English,0 i! j/ `3 s$ I" e/ K* C7 d* M, F
- sum(fraction)as score
2 m2 z- `! a$ ` X - from t_score group by Subject)t
复制代码方法三: with rollup - select " ^0 r7 q8 b4 w5 u: W/ S
- ifnull(name,'TOll') name,
# I4 ]9 @( i& W0 Y - sum(if(Subject='语文',Fraction,0)) as 语文,
5 p. `$ s: J0 p5 [ - sum(if(Subject='英语',Fraction,0)) as 英语,/ `0 ]* R9 u" r; F+ ~
- sum(if(Subject='数学',Fraction,0))as 数学,7 G9 ~$ ]2 J' ?
- sum(Fraction) 总分
c5 E# p+ e! ^$ X. F' }; R5 {' h8 T - from t_score group by name with rollup
复制代码查询结果如下:
! _' p( ?# h0 u5 O* X1 B- M; G0 d5 f% l [5 W5 a. q" p
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|