mysql行列转换

myskya

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

- I3 o8 q2 S. o
  a( j  M; V0 L! B创建数据库、表

1. create database tests;
   8 }& `2 ^' K0 ?2 I6 G/ A
2. use tests;
   ; g, I. c6 G* ?& P& ?6 `
3. create table t_score(
   
4. id int primary key auto_increment,
   + a, i0 S! f! G: u( H2 `
5. name varchar(20) not null,  #名字
   
6. Subject varchar(10) not null, #科目
   
7. Fraction double default 0  #分数
   - g- H* ^$ r1 \# W1 Q! W9 @
8. );

复制代码

添加数据

1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
   7 b7 W* E5 A: X6 i
2.          ('王海', '语文', 86),
   $ G( V  N: P- ?9 R1 w
3.         ('王海', '数学', 83),
   
4.         ('王海', '英语', 93),
   5 P+ x) k3 F: E4 \
5.         ('陶俊', '语文', 88),
   
6.         ('陶俊', '数学', 84),
   
7.         ('陶俊', '英语', 94),
   - W6 F7 @6 |, f2 {7 T9 K: s* J
8.         ('刘可', '语文', 80),
   
9.         ('刘可', '数学', 86),
   
10.         ('刘可', '英语', 88),
    " e5 @9 ]1 `% j% k7 t- M) J
11.         ('李春', '语文', 89),
    
12.         ('李春', '数学', 80),
    
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   
3. sum(if(Subject='数学',Fraction,0))as 数学,
   
4. sum(if(Subject='英语',Fraction,0))as 英语,
   
5. round(AVG(Fraction),2) as 平均分,
   6 `+ E- C" ^# m7 O3 I' m0 \
6. SUM(Fraction) as 总分
   
7. from t_score group by name     
   
8. union
   ( Q1 r2 `/ \3 A7 z3 P- ^$ U
9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
   ) x- `9 r' {" |* C. a$ A# R
10. select 'TOTAL' as name,
    1 |! k( }$ t. ], l
11. sum(if(Subject='语文',Fraction,0)) as 语文,
    , u+ P5 |! z# ^) g9 s3 O6 V6 `
12. sum(if(Subject='数学',Fraction,0))as 数学,
    
13. sum(if(Subject='英语',Fraction,0))as 英语,
    : B) h7 H" L: u! g
14. SUM(Fraction) as 总分
    
15. from t_score group by Subject )t

复制代码

方式二:使用case

1. select  name as Name,
   
2. sum(case when Subject = '语文' then Fraction end) as Chinese,
   
3. sum(case when Subject = '数学' then Fraction end) as Math,
   9 Q8 Q7 V: J- y/ e
4. sum(case when Subject = '英语' then Fraction end) as English,
   8 }& s' }2 Y# b7 o) I( w
5. sum(fraction)as score
     l, W) W% ^$ s% m8 Z& w- K
6. from t_score group by name
   
7. UNION ALL
   ( S0 E9 W1 d( o) f; N2 y
8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
   ) F5 x' x$ w' D) F& n
9. select 'TOTAL' as name,
   
10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    ; s9 m- m- s) M) f+ B# F" C
11. sum(case when Subject = '数学' then Fraction end) as Math,
    8 W( R* j& q, G
12. sum(case when Subject = '英语' then Fraction end) as English,
      F% q% ^; F0 L# R; n" q) ^+ y0 c! A; j
13. sum(fraction)as score
    # t( s. f2 U! s: m. Z) p
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
   6 h6 k5 w, [( h  R9 T$ `8 y
2.         ifnull(name,'TOll') name,
   3 @, C6 [; S# q( ~3 b1 S4 Z: f
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   
6.        sum(Fraction) 总分
   
7.         from t_score group by name with rollup

复制代码

查询结果如下：

- l6 f  N! e: g0 o
* V' q  D% y8 _6 x& }