|
|
原始插入数据如下:
* g: v# u3 g6 a* B要求查询结果如下 :9 C7 }! g. D5 t% \7 f, Y
. U, p; i* f# d Y9 d+ J5 ]3 y) H7 _6 ?
创建数据库、表
; A, P. p, M/ h3 Y% X- create database tests;8 E+ W. @" r5 H ?6 ]% n/ ?2 a6 ?
- use tests; Q8 ~& @+ J7 c* b: {1 y" E
- create table t_score(
1 \+ R, p; a0 u) n - id int primary key auto_increment,8 G7 L- V2 Q: P9 Q/ q7 |8 G
- name varchar(20) not null, #名字9 t s- Y1 m, }3 _
- Subject varchar(10) not null, #科目
5 ?: q- n/ M/ E; n' d - Fraction double default 0 #分数& l, \; i6 Y" K' N7 r2 \
- );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES2 N( H& e5 ~9 X D* @8 [, A7 [3 T8 k
- ('王海', '语文', 86),
' Z# A' l) u8 D6 j/ d0 O1 X8 O9 D+ c - ('王海', '数学', 83),# D- a( u ]" F5 G6 d. _
- ('王海', '英语', 93),* N( n2 v ~7 t
- ('陶俊', '语文', 88),1 ~& B" U$ l2 J: m A! W7 Q
- ('陶俊', '数学', 84),& f7 j& s- _9 j- [- o# W' n$ _# Y
- ('陶俊', '英语', 94),1 [9 q, y- i n# Y+ g7 w" z
- ('刘可', '语文', 80),$ j# ^1 n* @6 r: N( T2 p& w6 V
- ('刘可', '数学', 86),
5 k. p, o# A! P' \ - ('刘可', '英语', 88),% X& ]' N6 K) Y, @- n. I
- ('李春', '语文', 89),, {; `2 k7 S7 P C* Z
- ('李春', '数学', 80),
- v8 D! ?0 J: ` - ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,( |% X! U$ w! q) b' v; j
- sum(if(Subject='语文',Fraction,0)) as 语文,
' ?7 m" e" A$ V- h+ Y - sum(if(Subject='数学',Fraction,0))as 数学, 9 g% w, h% V7 X4 a
- sum(if(Subject='英语',Fraction,0))as 英语,
% F5 ]/ I1 j$ \# N - round(AVG(Fraction),2) as 平均分,. Y! q6 l$ D/ O$ R; ^# }7 b' q! L
- SUM(Fraction) as 总分
7 V4 K" q# O. a# g. j - from t_score group by name 3 } \6 S C+ H" {2 P3 l- x
- union
& @& O& E' V4 T; T - select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(1 { ?( m! g& p7 H
- select 'TOTAL' as name,7 n) s; D* d3 r7 p, E% c' Y0 V
- sum(if(Subject='语文',Fraction,0)) as 语文,
- Y5 C7 H% a2 _, p$ X - sum(if(Subject='数学',Fraction,0))as 数学,
" q w: x$ s3 T2 b! D9 e - sum(if(Subject='英语',Fraction,0))as 英语,
0 ~! Z0 z% y: G# J1 ^ - SUM(Fraction) as 总分. O. J, @# W; W, _. D& o
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,
; ] H$ _! j3 x. ?: q - sum(case when Subject = '语文' then Fraction end) as Chinese,
7 G8 U6 |" y/ H - sum(case when Subject = '数学' then Fraction end) as Math,
, g) F" z8 d. N& Q' B - sum(case when Subject = '英语' then Fraction end) as English,: x2 z# ~: h+ g
- sum(fraction)as score' Z2 f( p4 V* n" n+ e& O
- from t_score group by name/ k* c. T4 Z! J( ?- Y! A2 e
- UNION ALL3 }! i- `6 [/ n& e* o3 h
- select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from() o/ b# H& F" L3 ?* l( f/ X; p
- select 'TOTAL' as name,0 u. r' e- E9 ^6 ~- b. n0 ` y
- sum(case when Subject = '语文' then Fraction end) as Chinese,
8 w# u; b8 a% m6 g3 T - sum(case when Subject = '数学' then Fraction end) as Math,: O9 R- J3 g& ?" t3 f) w: I
- sum(case when Subject = '英语' then Fraction end) as English,( s/ V, o% x6 ], {' l$ L0 y. Y6 g
- sum(fraction)as score5 d2 K4 O) S5 }
- from t_score group by Subject)t
复制代码方法三: with rollup - select 2 F! z- D# _/ R3 z2 `* T% }
- ifnull(name,'TOll') name,
0 V% L) Q' g& ]3 V - sum(if(Subject='语文',Fraction,0)) as 语文,
( I+ w% [2 ~$ N: l. G - sum(if(Subject='英语',Fraction,0)) as 英语,! k' n0 Q2 C' g9 L/ o
- sum(if(Subject='数学',Fraction,0))as 数学,( G: ~, w, \8 Y* O! _+ H
- sum(Fraction) 总分5 Y6 a3 }3 \. P' o+ Y
- from t_score group by name with rollup
复制代码查询结果如下: 4 T5 W3 y& t7 I1 V, a1 U$ l
7 q; _, l4 [; f+ ~' v# H q ` |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|