mysql行列转换

myskya

原始插入数据如下：
要求查询结果如下 ：
3 t" f2 N: V7 ^- _

创建数据库、表

1. create database tests;
   
2. use tests;
   
3. create table t_score(
   * h- B' o6 X$ e/ F7 [; o$ ~
4. id int primary key auto_increment,
   0 j. T/ n% _9 i$ W+ f
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
   
2.          ('王海', '语文', 86),
   
3.         ('王海', '数学', 83),
   7 ~& \) ]. E, Y1 k% m3 r: ~
4.         ('王海', '英语', 93),
   " d) T7 c& D  M  K+ I. z
5.         ('陶俊', '语文', 88),
   8 O9 s5 r# j( u+ A+ U( d& U/ }
6.         ('陶俊', '数学', 84),
   
7.         ('陶俊', '英语', 94),
   
8.         ('刘可', '语文', 80),
   1 ~+ Q) ?0 E$ c9 x7 z0 l
9.         ('刘可', '数学', 86),
   ' c: }: g7 j% ]+ q, a% B2 p+ b7 W4 _
10.         ('刘可', '英语', 88),
    
11.         ('李春', '语文', 89),
    : n( z; g3 a; f3 g; G; @
12.         ('李春', '数学', 80),
    . l% s# {0 ^# [8 `* h/ r& ?% W
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   
3. sum(if(Subject='数学',Fraction,0))as 数学,
   : g1 t# p$ \/ H" c* K
4. sum(if(Subject='英语',Fraction,0))as 英语,
   
5. round(AVG(Fraction),2) as 平均分,
   % [: C& A# P9 F+ {; y3 V3 r
6. SUM(Fraction) as 总分
   : H/ G" G+ W! }0 {9 W/ ]6 e
7. from t_score group by name     
   ! N( f7 q0 L. X1 X- c1 V
8. union
   
9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
   
10. select 'TOTAL' as name,
    4 R6 A/ m& _$ Q
11. sum(if(Subject='语文',Fraction,0)) as 语文,
    
12. sum(if(Subject='数学',Fraction,0))as 数学,
    
13. sum(if(Subject='英语',Fraction,0))as 英语,
    " F/ F2 g+ x; F. T' S' |3 L: `
14. SUM(Fraction) as 总分
    2 r) c' d) U+ y5 S3 u3 g  G
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,
   % z2 z) O1 g7 g! |3 {" d5 Z* }
4. sum(case when Subject = '英语' then Fraction end) as English,
   
5. sum(fraction)as score
   6 B7 V' [% s+ I4 Y" J
6. from t_score group by name
   / x* t1 N9 N) ~: g8 N2 b
7. UNION ALL
   2 c3 Z7 P4 ?3 M- Z( ~/ w2 ^
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,
   + i! S; R, z; _0 ?8 X& z" s4 d) O
10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    6 I: ?+ n/ O, x7 E$ M
11. sum(case when Subject = '数学' then Fraction end) as Math,
    ( p7 W# |6 ^1 E  Q2 R& H
12. sum(case when Subject = '英语' then Fraction end) as English,
    1 v* H# J; V$ i7 Y  o) b% c  e. Q
13. sum(fraction)as score
    
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
   
2.         ifnull(name,'TOll') name,
   
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   ! Z' d7 P7 C9 V5 N, A* P
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   6 L& ]; }3 p1 q  s3 X; O8 H3 Q
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   
6.        sum(Fraction) 总分
   1 E) l- ]9 A4 Z* a4 d! N
7.         from t_score group by name with rollup

复制代码

查询结果如下：

: n, x' J' P( ?. C