|
|
原始插入数据如下:
( K% @0 ]6 z# k1 l要求查询结果如下 :
% P' o7 f/ w' R! m% c! G9 O; f N- U: D3 `
: q- J: H4 t# H$ k
创建数据库、表1 C# `0 z, _& d5 u- k
- create database tests;4 w% M, Y$ J) q
- use tests;9 j" }. m/ y8 v Y4 T4 @/ v
- create table t_score(
& F; K/ k- _& }# Y3 s" z - id int primary key auto_increment,
3 {6 j; J/ o- S# I- j' o' ]- \ - name varchar(20) not null, #名字
4 P. R" x- S% G V" P$ l2 H+ ~ - Subject varchar(10) not null, #科目
3 P k/ O+ b" ^3 b) T8 B; O) S - Fraction double default 0 #分数
! e) S6 X4 j) e' Y- a - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES
( z4 Y* O' v; \1 l - ('王海', '语文', 86),
0 s1 O1 |) X7 {4 P - ('王海', '数学', 83),/ D% m9 t. m ~
- ('王海', '英语', 93),
, f. @( j7 o8 W5 q' | - ('陶俊', '语文', 88),4 p( h3 ^: g4 {
- ('陶俊', '数学', 84),
) A4 Y' N. E r& J2 B" }$ g - ('陶俊', '英语', 94),
& ~7 Y1 b& j' A2 {7 E! d9 b7 G - ('刘可', '语文', 80),
8 V; N" ?( i' d - ('刘可', '数学', 86),
4 `0 t8 H7 R, Q) c% _2 I3 @ - ('刘可', '英语', 88),6 e5 z9 k1 G ]
- ('李春', '语文', 89),
! h( @) J: g) m0 f6 k: O( u - ('李春', '数学', 80),, C0 U" g1 F! L
- ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,
P: r! `$ w- U `' ~ - sum(if(Subject='语文',Fraction,0)) as 语文,
" ^1 u: ?3 D. O, l6 I - sum(if(Subject='数学',Fraction,0))as 数学,
& U! D( q, E( ~# U - sum(if(Subject='英语',Fraction,0))as 英语,
$ I% m. g6 q; m$ n7 @, N - round(AVG(Fraction),2) as 平均分,
3 C7 @4 t& O- L: l8 [6 K - SUM(Fraction) as 总分
+ k# l) S6 C7 s) U8 k - from t_score group by name ! X5 T/ i/ M0 z& M0 K1 t7 j
- union9 s: ?" P" y. _% @
- select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(
" c# K8 p0 T' A; y1 {3 L' v - select 'TOTAL' as name,3 G5 b( h7 w3 C; j d
- sum(if(Subject='语文',Fraction,0)) as 语文,4 S; h# b) Z) D( S3 D% ~
- sum(if(Subject='数学',Fraction,0))as 数学, 5 u6 d7 a1 h+ e
- sum(if(Subject='英语',Fraction,0))as 英语,* @8 t$ t+ B9 l9 B
- SUM(Fraction) as 总分% e; S# l: m8 L }) \5 t
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,. Y* ]. N' T: }# ]# f. V: i
- sum(case when Subject = '语文' then Fraction end) as Chinese,* K4 f% a2 i7 _4 }
- sum(case when Subject = '数学' then Fraction end) as Math,
2 Q, O+ N8 f5 p! y. I - sum(case when Subject = '英语' then Fraction end) as English,
4 G* c+ d/ e& j& t7 [ - sum(fraction)as score
0 K" I& s0 A6 s" W7 _3 O - from t_score group by name
2 H1 R# R0 j6 I. s& S: a - UNION ALL
- Y# i/ F: ^) u4 R, R8 p2 h - select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(6 Y" {+ O7 o2 G F
- select 'TOTAL' as name,
8 F3 f9 @1 j4 f+ L - sum(case when Subject = '语文' then Fraction end) as Chinese,. ]/ C( L" n, ?& }! E
- sum(case when Subject = '数学' then Fraction end) as Math,6 j) b% X C( L, C
- sum(case when Subject = '英语' then Fraction end) as English,& P, O" ]0 D: f/ n4 }: B. b
- sum(fraction)as score& `( o: o, J/ i6 s
- from t_score group by Subject)t
复制代码方法三: with rollup - select " Y c9 A' m* s% E8 P, ]
- ifnull(name,'TOll') name,
9 J+ f3 F" w9 k - sum(if(Subject='语文',Fraction,0)) as 语文,5 H4 K. \$ a5 q# {
- sum(if(Subject='英语',Fraction,0)) as 英语,
; C7 C; S7 I. m' w - sum(if(Subject='数学',Fraction,0))as 数学,2 n1 ~: |, R$ s
- sum(Fraction) 总分
. N, `0 p. ]& e$ R& |0 n. x - from t_score group by name with rollup
复制代码查询结果如下: 1 Z9 c( e' R; h- ~- ^( f U
+ l U$ r; E& {$ e
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|