mysql行列转换

myskya

原始插入数据如下：
3 {, H4 g9 `1 S' N要求查询结果如下 ：

/ G3 K/ y" A/ B
创建数据库、表
! u" R0 T9 y+ d" h1 j1 Y; z

1. create database tests;
   
2. use tests;
   " s: l8 {1 |" }7 J3 X2 d% z
3. create table t_score(
   
4. id int primary key auto_increment,
   7 s7 ~4 O$ \. {- X
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
   & p0 H5 _( j% F3 w
2.          ('王海', '语文', 86),
   6 [/ v+ d+ {% w, [3 T$ Q) x
3.         ('王海', '数学', 83),
   ) X# h/ D+ Q6 x, o/ T* C3 D  C% ]
4.         ('王海', '英语', 93),
   
5.         ('陶俊', '语文', 88),
   
6.         ('陶俊', '数学', 84),
   
7.         ('陶俊', '英语', 94),
   
8.         ('刘可', '语文', 80),
   
9.         ('刘可', '数学', 86),
   
10.         ('刘可', '英语', 88),
    
11.         ('李春', '语文', 89),
    ( h' u! ]0 J4 N) s# K* ?! V4 T' Y
12.         ('李春', '数学', 80),
    ! g5 _3 ^4 J$ `+ j
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   , l2 N$ |8 F$ O) P+ V8 B
3. sum(if(Subject='数学',Fraction,0))as 数学,
   - ]( m5 S( b2 {0 J0 L) ^
4. sum(if(Subject='英语',Fraction,0))as 英语,
   7 l7 `" r# h, H+ T: o/ l7 B
5. round(AVG(Fraction),2) as 平均分,
     Z7 ?; k* g) ^2 k# y7 e
6. SUM(Fraction) as 总分
   
7. from t_score group by name     
   2 z1 `- F/ S( Z( R
8. union
   & F6 o( l2 {% j" w0 Y7 r; e! h; @  q
9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
   
10. select 'TOTAL' as name,
    
11. sum(if(Subject='语文',Fraction,0)) as 语文,
    
12. sum(if(Subject='数学',Fraction,0))as 数学,
    7 T7 K& Q5 [7 Z. Q! {: j0 t
13. sum(if(Subject='英语',Fraction,0))as 英语,
    ; z/ C1 D9 H! \7 `  Z
14. SUM(Fraction) as 总分
      }# L  W+ N$ b
15. from t_score group by Subject )t

复制代码

方式二:使用case

1. select  name as Name,
   - w, T" S2 Z% u& j7 W' s! i
2. sum(case when Subject = '语文' then Fraction end) as Chinese,
   4 ~% V7 O3 F0 |& G2 ~8 O+ [1 [
3. sum(case when Subject = '数学' then Fraction end) as Math,
   $ I- V  i8 w3 w; Y. Q+ T6 G
4. sum(case when Subject = '英语' then Fraction end) as English,
   
5. sum(fraction)as score
   
6. from t_score group by name
   
7. UNION ALL
   
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,
   
10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    * w$ h8 O1 E. z8 }# ?0 l
11. sum(case when Subject = '数学' then Fraction end) as Math,
    
12. sum(case when Subject = '英语' then Fraction end) as English,
    8 A; m" c8 `; g: Y5 R6 c" |' u* K* i
13. sum(fraction)as score
      J& L: S0 J/ T% X; T% r  v- S+ \
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
     Z$ M1 g$ w# t
2.         ifnull(name,'TOll') name,
   3 [. k  x: Q# k5 P( N' h
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   ( t6 `( H& p, {1 g/ ]
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   ' d1 a; X4 W6 S
6.        sum(Fraction) 总分
   
7.         from t_score group by name with rollup

复制代码

查询结果如下：

4 P3 M; }. e8 s0 B8 w; k* n
; E6 z- Y7 E  H1 t7 @  {