|
|
原始插入数据如下:6 ]9 r ]) U/ j/ {5 ^8 A( Q
要求查询结果如下 :
x1 U! A7 {& d/ G+ ~
% s' _6 L; a2 _9 w. B" V! B* o0 C8 Q6 s
创建数据库、表
5 Z6 e" K% h; K* `5 k% y- create database tests;
. }0 ~! @' t& u* u+ G; O - use tests;
2 B6 Q$ y, r8 h5 p4 c) E7 L* }/ P - create table t_score(
# X8 w1 }6 ^6 {1 r$ p - id int primary key auto_increment,0 e+ b4 R/ p3 r( N
- name varchar(20) not null, #名字
3 n2 _6 r1 N! F0 [% H$ }2 w - Subject varchar(10) not null, #科目
7 |: S ~) C. z# k; B% X- c3 z/ _9 A - Fraction double default 0 #分数
) H5 X" N/ a' S - );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES& h' b3 g$ A6 F* b
- ('王海', '语文', 86),
( J- |- b5 v) v' s: L; x - ('王海', '数学', 83),
/ |1 M+ x0 [+ [0 }3 w5 u4 m - ('王海', '英语', 93),
. i) R6 S1 J* h" p( s8 r - ('陶俊', '语文', 88),
6 y0 |- n$ m7 y: {$ q" ?9 s - ('陶俊', '数学', 84),
6 \' {2 M' W% t) W; x. B - ('陶俊', '英语', 94),
% {/ N/ M* y( ]# u - ('刘可', '语文', 80),
5 k8 @; z; L. H: ~0 X2 @ - ('刘可', '数学', 86),
+ o4 C! k3 y0 |0 p; b: F; { - ('刘可', '英语', 88),
9 V9 J3 C2 q/ y, \) W - ('李春', '语文', 89),
2 P# {' I5 _) k- I7 k/ r - ('李春', '数学', 80),- {) N$ F$ O2 Y; C
- ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,0 B1 W* A, r5 r; f
- sum(if(Subject='语文',Fraction,0)) as 语文,& S8 J0 X# Y, \9 i
- sum(if(Subject='数学',Fraction,0))as 数学,
3 c/ X7 r. Z' i6 [ - sum(if(Subject='英语',Fraction,0))as 英语,! x0 ?/ K, s: U6 {' |" v" e
- round(AVG(Fraction),2) as 平均分,3 | a& T1 `8 d# n5 N) @
- SUM(Fraction) as 总分
! r4 N7 s% _ A8 a- l3 z - from t_score group by name
4 ~7 _9 |- Z: }% q - union
- ~. y% R- j0 r6 ^' R8 O - select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(" N- A8 T f, N/ `+ Y7 R
- select 'TOTAL' as name,0 ^9 h g' Q! U! l+ Q
- sum(if(Subject='语文',Fraction,0)) as 语文,
% z1 R7 r' a: ~: A7 Y: Y - sum(if(Subject='数学',Fraction,0))as 数学, / f! T- A+ B3 g! Z; P p5 @
- sum(if(Subject='英语',Fraction,0))as 英语,
, W* ^8 y+ M) U: R; t. l4 S0 S1 Q - SUM(Fraction) as 总分4 z" t0 u* a! x
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,
( I% O* Z- a: r - sum(case when Subject = '语文' then Fraction end) as Chinese,/ Y8 e- t/ ?, Y0 J2 F' S3 \
- sum(case when Subject = '数学' then Fraction end) as Math,5 x$ m! |" S. s* N: I
- sum(case when Subject = '英语' then Fraction end) as English,0 o: R1 `2 T0 g! m- X
- sum(fraction)as score
4 P% q9 i$ \% E8 U4 _) V2 ^6 b$ M - from t_score group by name& u% {0 n1 L) ]; c; _
- UNION ALL
; ^4 I {' a0 C& a2 d' d - select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
4 ]$ S% i; p* r) ? - select 'TOTAL' as name,6 D, t" h/ T0 l- F' z7 M6 \
- sum(case when Subject = '语文' then Fraction end) as Chinese,
6 q; f. I5 I) s! M9 u& l0 W5 C - sum(case when Subject = '数学' then Fraction end) as Math,0 f4 B3 W5 w' X n% u' C
- sum(case when Subject = '英语' then Fraction end) as English,
5 ~4 p$ x: j. | - sum(fraction)as score
z& x; |/ W" p+ a. J5 Q1 Y5 p$ }* N - from t_score group by Subject)t
复制代码方法三: with rollup - select
* d6 g; @( J* v3 Y: b( {& e - ifnull(name,'TOll') name,
+ S) x" ^3 j: K# n1 B3 x1 V - sum(if(Subject='语文',Fraction,0)) as 语文,( O& s& M0 B! c9 S: Z$ i: n' B v
- sum(if(Subject='英语',Fraction,0)) as 英语,
! s7 @9 H- r; h# s; v0 ^ - sum(if(Subject='数学',Fraction,0))as 数学,) t% _* z4 W+ M6 ?+ |# O: }% o
- sum(Fraction) 总分
) ]# ]7 v) J' D- a4 i& }# L; x6 v - from t_score group by name with rollup
复制代码查询结果如下: 1 {- }8 J$ d9 ?6 p+ O C s
& t! o7 D, v) E3 }+ e
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|