mysql行列转换

myskya

原始插入数据如下：
要求查询结果如下 ：
  x1 U! A7 {& d/ G+ ~
% s' _6 L; a2 _9 w
创建数据库、表
5 Z6 e" K% h; K* `5 k% y

1. create database tests;
   . }0 ~! @' t& u* u+ G; O
2. use tests;
   2 B6 Q$ y, r8 h5 p4 c) E7 L* }/ P
3. create table t_score(
   # X8 w1 }6 ^6 {1 r$ p
4. id int primary key auto_increment,
   
5. name varchar(20) not null,  #名字
   3 n2 _6 r1 N! F0 [% H$ }2 w
6. Subject varchar(10) not null, #科目
   7 |: S  ~) C. z# k; B% X- c3 z/ _9 A
7. Fraction double default 0  #分数
   ) H5 X" N/ a' S
8. );

复制代码

添加数据

1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
   
2.          ('王海', '语文', 86),
   ( J- |- b5 v) v' s: L; x
3.         ('王海', '数学', 83),
   / |1 M+ x0 [+ [0 }3 w5 u4 m
4.         ('王海', '英语', 93),
   . i) R6 S1 J* h" p( s8 r
5.         ('陶俊', '语文', 88),
   6 y0 |- n$ m7 y: {$ q" ?9 s
6.         ('陶俊', '数学', 84),
   6 \' {2 M' W% t) W; x. B
7.         ('陶俊', '英语', 94),
   % {/ N/ M* y( ]# u
8.         ('刘可', '语文', 80),
   5 k8 @; z; L. H: ~0 X2 @
9.         ('刘可', '数学', 86),
   + o4 C! k3 y0 |0 p; b: F; {
10.         ('刘可', '英语', 88),
    9 V9 J3 C2 q/ y, \) W
11.         ('李春', '语文', 89),
    2 P# {' I5 _) k- I7 k/ r
12.         ('李春', '数学', 80),
    
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   
3. sum(if(Subject='数学',Fraction,0))as 数学,
   3 c/ X7 r. Z' i6 [
4. sum(if(Subject='英语',Fraction,0))as 英语,
   
5. round(AVG(Fraction),2) as 平均分,
   
6. SUM(Fraction) as 总分
   ! r4 N7 s% _  A8 a- l3 z
7. from t_score group by name     
   4 ~7 _9 |- Z: }% q
8. union
   - ~. y% R- j0 r6 ^' R8 O
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 语文,
    % z1 R7 r' a: ~: A7 Y: Y
12. sum(if(Subject='数学',Fraction,0))as 数学,
    
13. sum(if(Subject='英语',Fraction,0))as 英语,
    , W* ^8 y+ M) U: R; t. l4 S0 S1 Q
14. SUM(Fraction) as 总分
    
15. from t_score group by Subject )t

复制代码

方式二:使用case

1. select  name as Name,
   ( I% O* Z- a: r
2. sum(case when Subject = '语文' then Fraction end) as Chinese,
   
3. sum(case when Subject = '数学' then Fraction end) as Math,
   
4. sum(case when Subject = '英语' then Fraction end) as English,
   
5. sum(fraction)as score
   4 P% q9 i$ \% E8 U4 _) V2 ^6 b$ M
6. from t_score group by name
   
7. UNION ALL
   ; ^4 I  {' a0 C& a2 d' d
8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
   4 ]$ S% i; p* r) ?
9. select 'TOTAL' as name,
   
10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    6 q; f. I5 I) s! M9 u& l0 W5 C
11. sum(case when Subject = '数学' then Fraction end) as Math,
    
12. sum(case when Subject = '英语' then Fraction end) as English,
    5 ~4 p$ x: j. |
13. sum(fraction)as score
      z& x; |/ W" p+ a. J5 Q1 Y5 p$ }* N
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
   * d6 g; @( J* v3 Y: b( {& e
2.         ifnull(name,'TOll') name,
   + S) x" ^3 j: K# n1 B3 x1 V
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   ! s7 @9 H- r; h# s; v0 ^
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   
6.        sum(Fraction) 总分
   ) ]# ]7 v) J' D- a4 i& }# L; x6 v
7.         from t_score group by name with rollup

复制代码

查询结果如下：