mysql行列转换

myskya

原始插入数据如下：
' [. F2 _  s; l! u4 K要求查询结果如下 ：
/ c+ \5 g4 {8 N1 S9 S# E- ]
. k% l( y7 f* F  G
7 J" Y5 ~' d. ~* C( B% u0 W创建数据库、表
" s- ?) p5 a  K% J/ K6 b9 M

1. create database tests;
   6 T9 d' ], ]2 k; p/ Z( I
2. use tests;
   
3. create table t_score(
   
4. id int primary key auto_increment,
   
5. name varchar(20) not null,  #名字
   " D8 s- [2 e5 q; u
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),
   4 s% F% s$ ]  }" Y5 Y5 G" \
5.         ('陶俊', '语文', 88),
   
6.         ('陶俊', '数学', 84),
   , ]/ b8 ~9 l' [" Z
7.         ('陶俊', '英语', 94),
   1 K" c& f8 n; C# ^/ E
8.         ('刘可', '语文', 80),
   
9.         ('刘可', '数学', 86),
   
10.         ('刘可', '英语', 88),
      z% s5 }9 N& w* i& h2 J
11.         ('李春', '语文', 89),
    
12.         ('李春', '数学', 80),
    $ v. U. k: g5 Q) B
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   5 A" n2 E& b0 U  J* V, `- m
3. sum(if(Subject='数学',Fraction,0))as 数学,
   
4. sum(if(Subject='英语',Fraction,0))as 英语,
   / G  l7 Y* G. A. m9 f
5. round(AVG(Fraction),2) as 平均分,
   
6. SUM(Fraction) as 总分
   
7. from t_score group by name     
   3 p, v% }' j  k: C6 R% P# ]
8. union
   & }! `6 a! c& q7 Z- q6 H
9. select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score  from(
   + X+ _+ Z3 L) V+ s; K7 j' p& O6 ~
10. select 'TOTAL' as name,
    * ]8 j  t9 ~1 B: I
11. sum(if(Subject='语文',Fraction,0)) as 语文,
    
12. sum(if(Subject='数学',Fraction,0))as 数学,
    
13. sum(if(Subject='英语',Fraction,0))as 英语,
    $ j, n3 D8 N! s. e9 J2 V
14. SUM(Fraction) as 总分
    6 |8 j$ }2 K  ]9 t( k' H% M+ b; ~
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,
   
4. sum(case when Subject = '英语' then Fraction end) as English,
   
5. sum(fraction)as score
   1 E( k' F6 w: ]
6. from t_score group by name
   * P& s+ H! `0 B3 g
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,
   5 T7 n1 t2 m# u; F
10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    
11. sum(case when Subject = '数学' then Fraction end) as Math,
    
12. sum(case when Subject = '英语' then Fraction end) as English,
    
13. sum(fraction)as score
    8 B$ Q6 w! h; n3 L+ P
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
   " p! Q  ~. s. x0 W2 r
2.         ifnull(name,'TOll') name,
   7 v8 p- }9 o+ p1 z7 f
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
     o! {; R4 {$ e; v& N& \5 u
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   ; U6 ^/ d5 J3 P
6.        sum(Fraction) 总分
   - `( a8 W8 x/ U' b, H6 Q
7.         from t_score group by name with rollup

复制代码

查询结果如下：

5 G2 k) V; u& H4 I