mysql行列转换

myskya

原始插入数据如下：
要求查询结果如下 ：


2 Q# f. S+ D7 k创建数据库、表

1. create database tests;
   
2. use tests;
   ' M& s* E4 {  D# y- b8 |
3. create table t_score(
   
4. id int primary key auto_increment,
   . P/ a" O% ^( U+ |, J6 I8 |5 `% p1 P
5. name varchar(20) not null,  #名字
   3 _" A/ e6 n$ r! A! O1 z& y7 v
6. Subject varchar(10) not null, #科目
   5 H. u) l* _4 \, [& d1 m# O/ D) g
7. Fraction double default 0  #分数
   6 Z: d0 G0 M/ J$ u( F
8. );

复制代码

添加数据

1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
   2 ?  E  x9 f" U% |/ ~! n- X
2.          ('王海', '语文', 86),
   . f/ `2 Y' Z0 ]1 Z5 f
3.         ('王海', '数学', 83),
   
4.         ('王海', '英语', 93),
   5 h5 a1 X% s$ x. E+ a. p% k
5.         ('陶俊', '语文', 88),
   
6.         ('陶俊', '数学', 84),
   ; }& B7 C9 T# ]$ D
7.         ('陶俊', '英语', 94),
   : m% N# A% ^' @) L! }: S
8.         ('刘可', '语文', 80),
   
9.         ('刘可', '数学', 86),
   6 H9 J7 C; u9 ~$ P% T1 o5 v* {
10.         ('刘可', '英语', 88),
    
11.         ('李春', '语文', 89),
    / O) B  w+ z; k# C
12.         ('李春', '数学', 80),
    
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   " c, t; z& G$ U+ g1 x5 @. Y
3. sum(if(Subject='数学',Fraction,0))as 数学,
   8 D) e9 O/ [6 c0 Z9 U! m' M
4. sum(if(Subject='英语',Fraction,0))as 英语,
   
5. round(AVG(Fraction),2) as 平均分,
   % K3 e) w. n% L9 s1 @( f
6. SUM(Fraction) as 总分
   
7. from t_score group by name     
   
8. union
   % w1 M8 W, f4 m0 j1 A% @
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 语文,
    ! E; z/ B. C: U, E, A) w
12. sum(if(Subject='数学',Fraction,0))as 数学,
    / x. @- t# `( ]7 L
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,
   ) [) J9 P6 m6 M& ]# f
2. sum(case when Subject = '语文' then Fraction end) as Chinese,
   7 ?- [- Q7 K$ N, _
3. sum(case when Subject = '数学' then Fraction end) as Math,
   $ J8 S; x) Y! L, R# p1 p3 _. {6 |
4. sum(case when Subject = '英语' then Fraction end) as English,
   ; \2 h: p' b& `  T0 T2 x: A
5. sum(fraction)as score
   
6. from t_score group by name
   3 Q' p& G4 I# G- n+ j0 t& N. N* V
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(
   2 I" E$ d  q8 H$ t
9. select 'TOTAL' as name,
   / O: W2 R3 [3 D# L
10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    ) ^+ A' W2 I) b4 y1 ~
11. sum(case when Subject = '数学' then Fraction end) as Math,
    $ K" R# w$ Y0 g. j. J  r  S3 C
12. sum(case when Subject = '英语' then Fraction end) as English,
    - [( a' G- G0 }  R
13. sum(fraction)as score
    5 `  m, [$ _  q" l4 g3 t
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
   
2.         ifnull(name,'TOll') name,
   2 i/ B8 b0 F# F( Y5 l' u; j
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   # @- P' U7 {+ M( s  I
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   5 n8 Z( B: m* a3 T# l" {2 i
6.        sum(Fraction) 总分
   
7.         from t_score group by name with rollup

复制代码

查询结果如下：

) j! [4 J- c, d+ H% i, A# M
" A( {; g: X4 j" C) E- x4 `' O+ B