mysql行列转换

myskya

原始插入数据如下：
/ B# {; N  [; K% q8 P要求查询结果如下 ：


/ y, e& j1 g- B: }$ {创建数据库、表
! O* d* |" _$ M- h& M5 L$ W

1. create database tests;
   
2. use tests;
   
3. create table t_score(
   9 Q4 v0 E' s+ u- D  x2 Y/ v
4. id int primary key auto_increment,
   
5. name varchar(20) not null,  #名字
   : }( [! Z: _7 e$ a) _7 W& L
6. Subject varchar(10) not null, #科目
   
7. Fraction double default 0  #分数
   1 e4 R" k( X: ^' G" U' Z4 F
8. );

复制代码

添加数据

1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
   % h( S* X5 ~$ Q9 _; M% \* r& ]
2.          ('王海', '语文', 86),
   
3.         ('王海', '数学', 83),
   ( w3 g& k2 [; T3 c3 l% l" r: T: i
4.         ('王海', '英语', 93),
   
5.         ('陶俊', '语文', 88),
   
6.         ('陶俊', '数学', 84),
   0 T7 x5 T1 Q4 Z
7.         ('陶俊', '英语', 94),
   6 W; i( t7 _2 N4 P
8.         ('刘可', '语文', 80),
   % {1 Y! {. v4 W5 |1 W: A7 F( ]
9.         ('刘可', '数学', 86),
   
10.         ('刘可', '英语', 88),
    
11.         ('李春', '语文', 89),
    ! I( u# M: M% x: f0 u* L) \
12.         ('李春', '数学', 80),
    
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   
3. sum(if(Subject='数学',Fraction,0))as 数学,
   6 f# P$ O6 F+ I* U: h" |( i( i
4. sum(if(Subject='英语',Fraction,0))as 英语,
   : o! b6 j, ^" o7 ^: D$ H) f
5. round(AVG(Fraction),2) as 平均分,
   $ i9 @6 }) J* W9 `3 X2 |0 s' S
6. SUM(Fraction) as 总分
   
7. from t_score group by name     
   
8. union
   
9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
   0 {( h. K1 n5 A# M0 w
10. select 'TOTAL' as name,
    
11. sum(if(Subject='语文',Fraction,0)) as 语文,
    4 U1 ]& z/ @" R/ r
12. sum(if(Subject='数学',Fraction,0))as 数学,
    4 j0 d( y2 X6 V, W- v, _0 B
13. sum(if(Subject='英语',Fraction,0))as 英语,
    9 U% n  ?. ^6 R/ b+ |
14. SUM(Fraction) as 总分
    - J  O. u# O( \, U( g- t
15. from t_score group by Subject )t

复制代码

方式二:使用case

1. select  name as Name,
   1 ?$ Q; J5 p, ]. y" y
2. sum(case when Subject = '语文' then Fraction end) as Chinese,
   - L' |. W: y  m+ {, T7 ?9 p, g
3. sum(case when Subject = '数学' then Fraction end) as Math,
   ! h5 v8 E6 ?0 \; A: N
4. sum(case when Subject = '英语' then Fraction end) as English,
   $ B- P4 c4 C( W$ ~; g, B% L
5. sum(fraction)as score
   . K- @: s2 w- I8 c
6. from t_score group by name
   # l: C: u9 o7 H" q$ }$ f6 r
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,
   & U% @* Z! K8 Q9 P: t
10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    5 f, }, U6 u4 i+ P; N  ?6 e
11. sum(case when Subject = '数学' then Fraction end) as Math,
    # n2 c: w6 x5 I! t* J7 ?( D5 }2 Z7 ^
12. sum(case when Subject = '英语' then Fraction end) as English,
      Q4 J+ [5 z  M
13. sum(fraction)as score
    9 j  w  T9 e% U4 H
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
   + z& @3 \$ `* f6 U
2.         ifnull(name,'TOll') name,
   . U8 S- U& L2 ?& V
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   ; d: s* f. ]" {% I
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   
6.        sum(Fraction) 总分
   8 e- J8 t; G7 Z5 f. [" W) E
7.         from t_score group by name with rollup

复制代码

查询结果如下：