mysql行列转换

myskya

原始插入数据如下：
6 ?$ V$ H$ o, J要求查询结果如下 ：
4 E8 e- S. E3 u( F- R8 _* Q

创建数据库、表

1. create database tests;
   + P* [  [, L/ ]$ T, M7 b
2. use tests;
   5 s' l9 ^' f: V8 A! a. p
3. create table t_score(
   
4. id int primary key auto_increment,
   / h& U5 L0 L1 n6 e
5. name varchar(20) not null,  #名字
   5 g/ u0 [* t7 V& J
6. Subject varchar(10) not null, #科目
   
7. Fraction double default 0  #分数
   
8. );

复制代码

添加数据

1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
   9 m; o) J  e+ ]3 Z
2.          ('王海', '语文', 86),
   
3.         ('王海', '数学', 83),
   
4.         ('王海', '英语', 93),
   4 ]( u2 ~7 [, I; _$ W! i+ S1 J/ l
5.         ('陶俊', '语文', 88),
   / F9 J: r) G! @
6.         ('陶俊', '数学', 84),
   
7.         ('陶俊', '英语', 94),
   
8.         ('刘可', '语文', 80),
     @& }' x( E( c. e
9.         ('刘可', '数学', 86),
   
10.         ('刘可', '英语', 88),
    ; g# m# ?7 T" p: v
11.         ('李春', '语文', 89),
    * k: n! }( l$ E% G
12.         ('李春', '数学', 80),
    
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   
3. sum(if(Subject='数学',Fraction,0))as 数学,
   8 i1 G4 d3 I3 Q2 x! J6 X
4. sum(if(Subject='英语',Fraction,0))as 英语,
   % n8 P& g3 A! H4 J
5. round(AVG(Fraction),2) as 平均分,
   ' u$ H" f; y2 Z9 }+ J7 ], x
6. SUM(Fraction) as 总分
   4 k. y6 ?6 O( a+ h. |0 n) C4 g7 B, a
7. from t_score group by name     
   
8. union
   2 R& X5 m5 ]  j9 m8 n( w3 B
9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
   & x5 \( `+ |( l
10. select 'TOTAL' as name,
    
11. sum(if(Subject='语文',Fraction,0)) as 语文,
    3 }2 \6 ~6 n) l$ n! S! C
12. sum(if(Subject='数学',Fraction,0))as 数学,
    $ J. r0 R0 S, _
13. sum(if(Subject='英语',Fraction,0))as 英语,
    
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,
   2 [! E" q; O: @+ M+ V& x/ P
3. sum(case when Subject = '数学' then Fraction end) as Math,
   / i. ^* h. `4 [. X( }
4. sum(case when Subject = '英语' then Fraction end) as English,
   + w3 E, M& x! n4 j" k& e, G
5. sum(fraction)as score
   $ G: n7 U& R& e8 Z2 c
6. from t_score group by name
   ) V% S+ |, q3 |1 Z7 w
7. UNION ALL
   $ D3 g- {7 R  w0 d( d( N' G5 X
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,
    " ~: x, F* o: R
11. sum(case when Subject = '数学' then Fraction end) as Math,
    5 ^9 v1 w9 R  _, B3 f# @- o
12. sum(case when Subject = '英语' then Fraction end) as English,
    
13. sum(fraction)as score
    6 l8 |/ T; _5 Y: E% i5 M* W
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
   
2.         ifnull(name,'TOll') name,
     s/ J$ K# T) J7 t
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   
6.        sum(Fraction) 总分
   6 R( [& t0 X4 e0 a0 l8 {. E. q
7.         from t_score group by name with rollup

复制代码

查询结果如下：

+ X4 e. A+ ]4 E" p& t, T$ s
2 J+ n2 T  I6 b: g% i