|
|
原始插入数据如下:# h! a7 S: ?! H7 N5 r: u
要求查询结果如下 :
/ p# D: D6 V: |
6 z2 H4 R3 l) C. g+ g6 f% r" k- Z+ ~2 n; X5 t* p' ] \( N
创建数据库、表
( Q2 I- H0 _. i! H- C. R- create database tests;
& X0 K( |+ U# O$ i8 g2 W - use tests;- {0 w5 V, R9 A$ T1 k
- create table t_score(
; u7 g; |% W7 Y$ \ - id int primary key auto_increment,
; x- d: }# j: o. P F - name varchar(20) not null, #名字
% A* v. t; I4 q, O8 }! i4 ] - Subject varchar(10) not null, #科目
( U: }8 w" I- J2 F - Fraction double default 0 #分数6 {/ y* Y1 B' n
- );
复制代码添加数据 - INSERT INTO `t_score`(name,Subject,Fraction) VALUES
$ [ \5 m+ E. o: e6 [& \; y - ('王海', '语文', 86),
7 d) A# P! m) J8 I - ('王海', '数学', 83),$ i- M0 a' H2 ?) G, R
- ('王海', '英语', 93),0 {; C5 p) B a# S, [: V- m
- ('陶俊', '语文', 88),
( B$ ?% ^0 l+ M2 F2 V' Z( ] - ('陶俊', '数学', 84),. ~. | l* V+ a! N5 C# G; a% P/ \
- ('陶俊', '英语', 94),
! Q8 a$ w$ K# ?+ `* p0 ] - ('刘可', '语文', 80),
" f) c- S, \0 i' b& _ - ('刘可', '数学', 86),
7 A; I9 c# r3 O- y/ ^! L; A - ('刘可', '英语', 88),* [: x! a8 y: L4 ~( q
- ('李春', '语文', 89),% H) d( U3 J' ]- S- \
- ('李春', '数学', 80),3 L2 X3 F: u6 \0 Y) H+ q' n
- ('李春', '英语', 87);
复制代码方式一:使用if - select name as 名字 ,& o3 N5 z8 z( V2 {
- sum(if(Subject='语文',Fraction,0)) as 语文,$ [, Z0 k3 }7 @% `2 P' Y( u
- sum(if(Subject='数学',Fraction,0))as 数学, 4 i. k3 y( z: O8 _$ U
- sum(if(Subject='英语',Fraction,0))as 英语,
/ j. [6 H7 C) a* x- A7 F. |% e% | - round(AVG(Fraction),2) as 平均分,
9 @ B I: m! \# \3 y+ F$ h - SUM(Fraction) as 总分
5 i+ x; j, i: t- o: E. W) G - from t_score group by name
: T: f" U+ w6 [: |) i - union. b& H6 W* u5 |& n
- select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from(+ k/ {( e5 d ]9 K: ?" m8 L$ \
- select 'TOTAL' as name,
0 D# h4 t( b5 k+ U% z1 R - sum(if(Subject='语文',Fraction,0)) as 语文,: M9 U, L5 R% G0 G0 a* L# |
- sum(if(Subject='数学',Fraction,0))as 数学,
; b: y; F, ^2 z8 [+ k: H- R# v - sum(if(Subject='英语',Fraction,0))as 英语,
% |. h5 d2 R9 \: }% q. n - SUM(Fraction) as 总分* ^/ P" d' [1 x9 R6 O l4 P
- from t_score group by Subject )t
复制代码方式二:使用case - select name as Name,1 {' R7 E( W3 W
- sum(case when Subject = '语文' then Fraction end) as Chinese, g5 d/ y Q8 t: V# k
- sum(case when Subject = '数学' then Fraction end) as Math, j! z1 ?, x, w4 d# r0 ^
- sum(case when Subject = '英语' then Fraction end) as English,0 Y6 x1 \1 C y) e
- sum(fraction)as score$ t3 X- B1 E' d
- from t_score group by name
; N+ j' m! W6 B9 A2 O& f7 V, h( @7 C( f) j - UNION ALL$ G0 V4 _3 k5 `6 N7 d
- select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
& x# `1 R/ a8 z) a9 e& @- U5 G' y# s# e5 k - select 'TOTAL' as name,; | ^- i" y% g
- sum(case when Subject = '语文' then Fraction end) as Chinese,9 m0 M1 f. ?% @1 B/ P) M7 ]) D
- sum(case when Subject = '数学' then Fraction end) as Math,
5 W0 m: d2 n9 @ - sum(case when Subject = '英语' then Fraction end) as English,
# X# k0 p$ @7 [2 }8 a8 D7 r - sum(fraction)as score9 V" m6 B2 |5 t, e; _0 I
- from t_score group by Subject)t
复制代码方法三: with rollup - select
! u- ]2 j) P% U4 E, _& B9 J - ifnull(name,'TOll') name,
" {9 r9 g; R2 O - sum(if(Subject='语文',Fraction,0)) as 语文,1 Y( v, D/ |" Y- j2 h9 @+ K
- sum(if(Subject='英语',Fraction,0)) as 英语,1 x6 L6 C P9 S& w, B; X
- sum(if(Subject='数学',Fraction,0))as 数学,
4 m: P$ ?* f$ S E5 s - sum(Fraction) 总分 F, o8 Q3 A; |. `
- from t_score group by name with rollup
复制代码查询结果如下:
* q ~1 Q: p2 ~ s$ R
; a, o) u5 p9 L: L* _5 q% n |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
|