mysql行列转换

myskya

原始插入数据如下：
) X* X  f* K4 y1 e4 k$ q) `要求查询结果如下 ：

% ^$ i$ K6 W0 K/ p- N6 z* f5 Y9 g# x& z
创建数据库、表
3 B* O$ _. k1 T  b- u1 Y+ J( t

1. create database tests;
   
2. use tests;
   6 i  \7 s$ z$ [, P/ Z% X
3. create table t_score(
   ! Q, A- v- n* l$ O% j) T* P
4. id int primary key auto_increment,
   
5. name varchar(20) not null,  #名字
   + c4 z' v% p) C" i
6. Subject varchar(10) not null, #科目
   6 o5 C0 C, q* p, t( o
7. Fraction double default 0  #分数
     `7 ~$ L# f/ t  h0 z
8. );

复制代码

添加数据

1. INSERT INTO `t_score`(name,Subject,Fraction) VALUES
   1 C0 }" d7 H5 i( R+ W" q
2.          ('王海', '语文', 86),
   
3.         ('王海', '数学', 83),
   
4.         ('王海', '英语', 93),
   
5.         ('陶俊', '语文', 88),
   
6.         ('陶俊', '数学', 84),
   
7.         ('陶俊', '英语', 94),
   
8.         ('刘可', '语文', 80),
   8 k; \+ s" e* j2 H
9.         ('刘可', '数学', 86),
   ; y) K+ Z+ U- [) h; `& w* {1 T
10.         ('刘可', '英语', 88),
    
11.         ('李春', '语文', 89),
    
12.         ('李春', '数学', 80),
    % D9 a8 e* U7 p$ U( j
13.         ('李春', '英语', 87);

复制代码

方式一:使用if

1. select name as 名字 ,
   
2. sum(if(Subject='语文',Fraction,0)) as 语文,
   
3. sum(if(Subject='数学',Fraction,0))as 数学,
   
4. sum(if(Subject='英语',Fraction,0))as 英语,
   
5. round(AVG(Fraction),2) as 平均分,
   2 B% }" s4 `4 p1 |# E1 L2 D+ g9 R
6. SUM(Fraction) as 总分
   0 c+ v+ \8 _# g) L/ U! J% a
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(
   
10. select 'TOTAL' as name,
    + i' ?6 g1 @% V; M; k! W) L( G# h
11. sum(if(Subject='语文',Fraction,0)) as 语文,
    
12. sum(if(Subject='数学',Fraction,0))as 数学,
    0 Y& s$ c$ g- _' B; J
13. sum(if(Subject='英语',Fraction,0))as 英语,
    # t+ M( Q# J; `
14. SUM(Fraction) as 总分
    
15. from t_score group by Subject )t

复制代码

方式二:使用case

1. select  name as Name,
   5 Y* S# G+ n; o
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,
   0 _$ Y, z1 h; R) {" M! r- H' |' S
5. sum(fraction)as score
   
6. from t_score group by name
   
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(
   ) {8 Z9 z+ |3 s: `# Y8 f' [
9. select 'TOTAL' as name,
   4 u0 o  \3 \+ ^0 f" g( d' ?
10. sum(case when Subject = '语文' then Fraction end) as Chinese,
    : H; X5 G9 `! r! S
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
    7 |3 y+ t6 o9 m3 w! ]
14. from t_score group by Subject)t

复制代码

方法三: with rollup

1. select
   / [# v2 A) j) p6 Z  _# [
2.         ifnull(name,'TOll') name,
   
3.         sum(if(Subject='语文',Fraction,0)) as 语文,
   & l3 b1 U  E8 Z! j5 {+ ?. c" Q# {) m
4.        sum(if(Subject='英语',Fraction,0)) as 英语,
   
5.        sum(if(Subject='数学',Fraction,0))as 数学,
   
6.        sum(Fraction) 总分
   
7.         from t_score group by name with rollup

复制代码

查询结果如下：