mysql行列转换

myskya

原始插入数据如下：
# b/ Y4 g0 S: [6 B1 Y* I& o# |要求查询结果如下 ：

  S5 Z0 z6 c: f6 p
5 @) z) Y& E  l: n+ J创建数据库、表
" B8 G. W6 N. ~! o% B

1. create database tests;
     O9 w+ Q7 Z, t
2. use tests;
   
3. create table t_score(
   - I9 V. r+ M% B; q2 r. _8 }3 y
4. id int primary key auto_increment,
   6 Z/ _7 y1 D) o$ o
5. name varchar(20) not null,  #名字
   ; J8 I( A: H) H8 [4 |9 r; N3 o
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),
   
4.         ('王海', '英语', 93),
   $ f9 o, d" L% x3 _9 P$ s
5.         ('陶俊', '语文', 88),
   
6.         ('陶俊', '数学', 84),
   1 S; K$ G, ~" U: y  `
7.         ('陶俊', '英语', 94),
   + j% r( v0 \7 l$ n4 v  w7 o9 S7 h/ m
8.         ('刘可', '语文', 80),
   
9.         ('刘可', '数学', 86),
   " @0 H1 H) T) d) {6 ]
10.         ('刘可', '英语', 88),
    & j5 w$ d: q3 D' a
11.         ('李春', '语文', 89),
    " y  [9 x7 \; Q
12.         ('李春', '数学', 80),
    ; Z; N2 g* @& r/ t9 t! D) g
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   2 I/ m' x0 u, ^2 J" Z5 y
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   
3. sum(if(Subject='数学',Fraction,0))as 数学,
   # n- T+ A9 M8 ]; O1 _# O
4. sum(if(Subject='英语',Fraction,0))as 英语,
   " a2 ~# j4 }( p) V
5. round(AVG(Fraction),2) as 平均分,
   
6. SUM(Fraction) as 总分
   
7. from t_score group by name     
   ' \* K2 O' z1 K" z/ t/ i8 Z
8. union
   
9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
   * Z0 w" A/ k% n: I+ s
10. select 'TOTAL' as name,
    ; v- `# [' i- G4 v- t3 o+ V8 L/ |
11. sum(if(Subject='语文',Fraction,0)) as 语文,
    & o9 q1 k6 k( \4 U9 A: b( g8 Z
12. sum(if(Subject='数学',Fraction,0))as 数学,
    
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,
   . {7 m( j' \. P2 Q. o  c9 i% l
2. sum(case when Subject = '语文' then Fraction end) as Chinese,
   / K# D2 L9 |+ R5 o4 O5 l
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
   
6. from t_score group by name
   4 }- F" ~2 S' B) u+ |' u
7. UNION ALL
   * ]  ?5 r/ _6 \7 g, v$ z
8. select  name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from(
   ; w: d. U+ P8 T/ r5 p4 Q' \0 K
9. select 'TOTAL' as name,
   
10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    % L+ Q0 K$ T2 P( |8 I% p5 Y
11. sum(case when Subject = '数学' then Fraction end) as Math,
    5 g- L# p3 l: f, O% g' h
12. sum(case when Subject = '英语' then Fraction end) as English,
    2 _$ T# |% t- W6 z
13. sum(fraction)as score
    2 w5 }" M6 K6 F# W
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
   5 q% ]: l: p, ~7 c
2.         ifnull(name,'TOll') name,
   ( N$ ]  _& ]6 B! }3 ?2 V
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   & o( P% \; p6 ], W( }$ G& ~
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   ; C7 _$ d; d# C, ~& {; n
6.        sum(Fraction) 总分
   
7.         from t_score group by name with rollup

复制代码

查询结果如下：

3 g% w: q* ]2 O* t+ c7 j+ Y
$ \$ f( _, D6 n# Z8 M6 ?) W