mysql行列转换

myskya

原始插入数据如下：
: a) h# \! e: i) [6 B/ j9 P要求查询结果如下 ：
3 J) g$ C) f1 E4 K" S- V  @: g* ?8 e

$ \! c1 D$ e" ]: \7 l9 C" z创建数据库、表

1. create database tests;
   
2. use tests;
   
3. create table t_score(
   ' t8 M. [( k; E) H3 w+ K
4. id int primary key auto_increment,
   
5. name varchar(20) not null,  #名字
   
6. Subject varchar(10) not null, #科目
   " K+ s. r' C' q5 m
7. Fraction double default 0  #分数
   
8. );

复制代码

添加数据

1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
   ' x( F0 ]4 c/ E% ]" t5 r' h9 y
2.          ('王海', '语文', 86),
   : H. r: i8 W8 t
3.         ('王海', '数学', 83),
   
4.         ('王海', '英语', 93),
   
5.         ('陶俊', '语文', 88),
   
6.         ('陶俊', '数学', 84),
   8 u0 m$ t& F, M
7.         ('陶俊', '英语', 94),
   
8.         ('刘可', '语文', 80),
   
9.         ('刘可', '数学', 86),
   + ^6 u" e0 I- r
10.         ('刘可', '英语', 88),
    
11.         ('李春', '语文', 89),
    2 |. U' }, v* Y" P
12.         ('李春', '数学', 80),
    # K  y; ]5 v4 N. |
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   - ^: c0 m  `9 M1 I
3. sum(if(Subject='数学',Fraction,0))as 数学,
   
4. sum(if(Subject='英语',Fraction,0))as 英语,
   
5. round(AVG(Fraction),2) as 平均分,
     u9 l" {  C2 h1 p: O7 m7 ?
6. SUM(Fraction) as 总分
   
7. from t_score group by name     
   & x# U. U. ~; S( E
8. union
   
9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
   & B  R' T. J7 `( a( Z, Q+ e" s+ j
10. select 'TOTAL' as name,
      e, S" k. L. `4 b: V% h  c3 C
11. sum(if(Subject='语文',Fraction,0)) as 语文,
    9 o5 ]( t# ~/ A4 Q1 p
12. sum(if(Subject='数学',Fraction,0))as 数学,
    
13. sum(if(Subject='英语',Fraction,0))as 英语,
    & g" X: P+ E: B  Z$ H& m& F" M% e
14. SUM(Fraction) as 总分
    6 p  w+ }& B* y" W) u/ `2 g% ]
15. from t_score group by Subject )t

复制代码

方式二:使用case

1. select  name as Name,
   $ \# w0 v2 F' b2 \
2. sum(case when Subject = '语文' then Fraction end) as Chinese,
   / H1 J1 ?" s* y; n' z7 ^! z: d
3. sum(case when Subject = '数学' then Fraction end) as Math,
   ) A9 t0 o# H3 ?" o
4. sum(case when Subject = '英语' then Fraction end) as English,
   % W# I  ~9 r4 R, P- N& |- l' ^
5. sum(fraction)as score
   2 l- m3 K4 I8 p! Q: x. Z- ?
6. from t_score group by name
   # U* O" c4 y* Z- f$ s3 d
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,
     q6 X4 s# w2 n4 z. r1 L- ~) \+ X4 V
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,
    
13. sum(fraction)as score
    
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
   
2.         ifnull(name,'TOll') name,
   + t+ b$ y& ~- X8 Y$ Q; u
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   ( O) F, r" X1 j: e# k' Q
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   5 I* F5 H. k: `8 i1 p4 ?0 B
6.        sum(Fraction) 总分
   ( I5 _7 n! O2 x; |4 c  D2 D
7.         from t_score group by name with rollup

复制代码

查询结果如下：

5 N) s; U- H- i0 l! w
# F) [7 m4 m( N# }% x# F: u5 C! |