mysql行列转换

柠檬班的Linux、MySQL、Java成绩保存在数据表 tb_lemon_grade中，

表中字段id，student_name，course，score分别表示id，学生姓名，课程名称，课程成绩
请：行列转换，从图１转换成最后一张图的数据

一：创建表

drop table if exists tb_lemon_grade;
CREATE TABLE tb_lemon_grade (
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(20) DEFAULT NULL,
course VARCHAR(20) DEFAULT NULL,
score FLOAT DEFAULT '0'
) engine=INNODB default charset=utf8;

 

 

二：初始化数据

INSERT INTO tb_lemon_grade (student_name, course, score) VALUES
("张三", "Linux", 85),("张三", "MySQL", 92),("张三", "Java", 87),("李四", "Linux", 96),
("李四", "MySQL", 89),("李四", "Java", 100),("王五", "Linux", 91),("王五", "MySQL", 83),("王五", "Java", 98);

 

三：首先我们查询出所有数据,这个结果和我们的图1是一样的

select * from tb_lemon_grade;

 

四：使用常量列输出我们的目标结构

可以看到结果已经和我们的图二非常接近了

select student_name,0 'Linux', 0 'MySQL', 0 'score'  from tb_lemon_grade;

 

五：使用IF函数，替换我们的常量列，将成绩赋值到对应行的对应列

select student_name, 
if(course='Linux', score, 0) 'Linux',
if(course='MySQL', score, 0) 'MySQL',
if(course='java', score, 0) 'java'
from tb_lemon_grade;

 

运行SQL，结果如下所示：

六：我们来分析这个结果集，

在原始结构中，每一行表示了某个同学某一个科的成绩，以第一行为例，第一行是张三同学Linux的成绩，所以我们结果集中Linux有成绩为85，而其他两列MySQL和Java作为常量列，成绩为0。

再分析每个同学的成绩的所有行，如下图所示，每个方块内包含行中，就有该同学这门课程的成绩，并且该方块内其余行的成绩值为0。因此，不难想到，我们可以使用分组，通过分组提取出每科的成绩

七：分组，使用MAX函数取出最大值

（因为其中只有一行成绩为真实成绩，其他行值为0，所以最大值就是真实成绩）

select student_name,
max(if(course='Linux', score, 0)) 'Linux',
max(if(course='MySQL', score, 0)) 'MySQL',
max(if(course='java', score, 0)) 'java'
from tb_lemon_grade
group by student_name;

 

八：也可以分组后，对每行数据进行求和，使用SUM函数，语句和结果如下：

select student_name,
sum(if(course='Linux', score, 0)) 'Linux',
sum(if(course='MySQL', score, 0)) 'MySQL',
sum(if(course='java', score, 0)) 'java'
from tb_lemon_grade
group by student_name;

 

九：既然使用IF语句可以达到效果，那使用CASE语句也是同样的效果

分组，使用MAX聚合函数

select student_name,
max( case course when 'Linux' then score else 0 end) 'Linux',
max(case course when 'MySQL' then score else 0 end) 'MySQL',
max(case course when 'java' then score else 0 end)  'java'
from tb_lemon_grade
group by student_name;

 

结果如下图所示：

使用SUM，结果如下图所示

select student_name,
sum( case course when 'Linux' then score else 0 end) 'Linux',
sum(case course when 'MySQL' then score else 0 end) 'MySQL',
sum(case course when 'java' then score else 0 end)  'java'
from tb_lemon_grade
group by student_name;

 

总结：