mysql行列转换

myskya

原始插入数据如下：
要求查询结果如下 ：

6 ~$ V/ }/ o, u2 z! p4 a1 U4 u
创建数据库、表

1. create database tests;
   
2. use tests;
   , X. \. o8 V8 ~" c4 [2 Q
3. create table t_score(
   
4. id int primary key auto_increment,
   " @- _8 W; u/ f* X  Y! l: U
5. name varchar(20) not null,  #名字
   
6. Subject varchar(10) not null, #科目
   
7. Fraction double default 0  #分数
   
8. );

复制代码

添加数据

1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
   * d9 [2 d. A8 C$ y0 _+ P! L3 n
2.          ('王海', '语文', 86),
   
3.         ('王海', '数学', 83),
   
4.         ('王海', '英语', 93),
   + I3 X6 H/ G, T+ t9 D
5.         ('陶俊', '语文', 88),
   - L1 u- p& `6 r1 ?
6.         ('陶俊', '数学', 84),
   ) Y: T2 b( m& T
7.         ('陶俊', '英语', 94),
     b. J4 t# p6 ^: D( i3 b/ C/ @5 G5 V
8.         ('刘可', '语文', 80),
   
9.         ('刘可', '数学', 86),
   
10.         ('刘可', '英语', 88),
    6 `" `* H" Q% p
11.         ('李春', '语文', 89),
    # n2 z* t% y+ m' r# ~9 ~2 M
12.         ('李春', '数学', 80),
    9 k; o1 R; n1 P9 L( z2 m/ {4 R( K
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   
3. sum(if(Subject='数学',Fraction,0))as 数学,
   & q7 g) x, w* B
4. sum(if(Subject='英语',Fraction,0))as 英语,
   
5. round(AVG(Fraction),2) as 平均分,
   + f# n; ?& L! b; x: A$ @+ K
6. SUM(Fraction) as 总分
   
7. from t_score group by name     
   - [$ X4 m, n! u5 V( f/ T
8. union
   2 `, E8 [2 g% {# P+ ]& R: c
9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
   " @( p8 p8 E- S/ V$ U0 o8 Q
10. select 'TOTAL' as name,
    
11. sum(if(Subject='语文',Fraction,0)) as 语文,
    ( N4 S% F* }" J# u1 X( v" q6 s0 C
12. sum(if(Subject='数学',Fraction,0))as 数学,
    
13. sum(if(Subject='英语',Fraction,0))as 英语,
    " V( K. [& @/ ^! K, _; ]
14. SUM(Fraction) as 总分
    5 Q+ c% R7 F6 e- B2 H0 J
15. from t_score group by Subject )t

复制代码

方式二:使用case

1. select  name as Name,
   / {( C" z2 N! O' T5 }# d
2. sum(case when Subject = '语文' then Fraction end) as Chinese,
   2 r" X! w; E9 ?# [. H7 O+ M( B
3. sum(case when Subject = '数学' then Fraction end) as Math,
   
4. sum(case when Subject = '英语' then Fraction end) as English,
   
5. sum(fraction)as score
   
6. from t_score group by name
   & R( w! p( m: M7 @
7. UNION ALL
   + X9 b( Z/ ^3 ?3 Q! p8 Z: d& b
8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
   
9. select 'TOTAL' as name,
   3 w$ ]/ ~7 n7 N/ B
10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    
11. sum(case when Subject = '数学' then Fraction end) as Math,
    
12. sum(case when Subject = '英语' then Fraction end) as English,
    0 P& B; O# X. K; k( I
13. sum(fraction)as score
    5 G6 l7 s( Q. ^7 o7 E) a- c& h& n
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
     q# L& ~; V" N" S- X+ X4 X
2.         ifnull(name,'TOll') name,
   
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   ; c7 |/ Q4 ]# f! H' n$ |
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   6 J- \3 N4 n' f' f) F6 M
6.        sum(Fraction) 总分
   
7.         from t_score group by name with rollup

复制代码

查询结果如下：

4 [1 @( j* s* Y0 G! S4 Q