|
原始插入数据如下:" v- [, n- R# F8 ?1 i( a, g9 [$ [
要求查询结果如下 :
4 I2 @! F J) X) }8 i4 L" }. B u9 }4 y
8 ]: i. K. V3 g. t2 p9 Z, R
创建数据库、表( l! {! M5 m _8 `
- create database tests;
7 e5 n \6 S5 I5 w2 C' Y4 P9 R - use tests;$ _+ z1 Y! O: `. O- M
- create table t_score(
( H1 ]5 Z1 R- e - id int primary key auto_increment,- P9 a$ d9 g; i
- name varchar(20) not null, #名字
- Z) ]# q6 O% V" Z: K - Subject varchar(10) not null, #科目: E" ~ o6 o' R7 o
- Fraction double default 0 #分数. w( \9 |$ r( @& E
- );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES% ]$ N, D* W9 B0 Q/ P& y: Z
- ('王海', '语文', 86),
4 M6 F/ `: p( d# u+ R" k - ('王海', '数学', 83),% i- `+ a, z8 v1 A
- ('王海', '英语', 93),
* V2 n! T; D0 Z2 m5 ^# r0 c - ('陶俊', '语文', 88),5 i' j% g9 z6 N* g
- ('陶俊', '数学', 84),
# X, `( x& |6 e. j, q - ('陶俊', '英语', 94),1 L' T9 B2 }7 u- c5 t- d
- ('刘可', '语文', 80),; j! V4 g7 A3 ~% l
- ('刘可', '数学', 86),
1 `4 d V! J3 k6 S0 k% H3 c - ('刘可', '英语', 88),7 r; w( V9 I9 v6 A! H# h+ [' R
- ('李春', '语文', 89),
( z, L3 v. H: ]9 u; G9 X - ('李春', '数学', 80),8 g6 _7 ?% T- {# H) R: q- F- X
- ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,
. L2 I5 ~( @1 z" h" l - sum(if(Subject='语文',Fraction,0)) as 语文,: P/ ] q. k' L+ E$ g8 i" }7 }
- sum(if(Subject='数学',Fraction,0))as 数学, : o3 k* K9 b* B! |
- sum(if(Subject='英语',Fraction,0))as 英语," K' E+ T' S7 S: K6 X' E
- round(AVG(Fraction),2) as 平均分,
7 M2 Q* M- \4 Q* C - SUM(Fraction) as 总分
" y& c$ S, p* F4 U6 V& }9 } - from t_score group by name ( w: B6 @/ R% m, J+ _ ?
- union
* C5 [! B, k; O. K - select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(9 d, s" N: h; f7 x2 i( f
- select 'TOTAL' as name,
1 H* v+ v9 ^& D9 x8 P - sum(if(Subject='语文',Fraction,0)) as 语文,
) J2 {/ G) l: }: t8 i- ]5 D - sum(if(Subject='数学',Fraction,0))as 数学, ( a7 g, B8 g0 ^, F, |. X1 v
- sum(if(Subject='英语',Fraction,0))as 英语,
: z2 z* d( h& \ v3 n* n+ C - SUM(Fraction) as 总分0 v9 I) u- E4 G0 F
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,- z, _# L0 [$ c+ W q$ _
- sum(case when Subject = '语文' then Fraction end) as Chinese,% U/ X' l) [( q l6 N6 W2 t/ }: r2 x: v
- sum(case when Subject = '数学' then Fraction end) as Math,
7 n3 T- h8 A! k1 E0 U( A8 ` - sum(case when Subject = '英语' then Fraction end) as English," L" D# T% s, n! a* x
- sum(fraction)as score
7 \3 ~) K j1 q6 g& k - from t_score group by name+ t2 r. z) g1 {1 ^- Z
- UNION ALL
6 l" d; c0 L! u) u; Y - select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(4 i# f! Q, Q- I
- select 'TOTAL' as name,
/ j; k) y3 d8 e) E7 c) T$ N7 M - sum(case when Subject = '语文' then Fraction end) as Chinese,$ o* R6 c! H9 E# e% d
- sum(case when Subject = '数学' then Fraction end) as Math," a' A6 k) Z) ]
- sum(case when Subject = '英语' then Fraction end) as English,
8 ^5 C& q( X. f% J' v - sum(fraction)as score
9 G+ r7 @/ W9 P6 P! [. ] - from t_score group by Subject)t
复制代码方法三: with rollup - select
: |9 ] S& Z' X; W* g1 l* k# E S - ifnull(name,'TOll') name,
: Y6 |% t% {& r. t1 { a X - sum(if(Subject='语文',Fraction,0)) as 语文,9 V: f. z8 }, @1 P% \! M/ |
- sum(if(Subject='英语',Fraction,0)) as 英语,
' X. U! `' _0 i - sum(if(Subject='数学',Fraction,0))as 数学,
. S' J- p z O9 `' E, Y - sum(Fraction) 总分* l) {0 x, Z, ~! p3 A* {7 X
- from t_score group by name with rollup
复制代码查询结果如下: , [! }0 e& R4 I) k" V& e* V% {
% O+ J# G G" w% L3 d |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|