SQL经典面试题—行列转换
在我们的实际工作中，为了研发出功能完整、系统健壮的软件，需要我们测试人员想法设法的去挑出软件的问题，所以很多公司对测试人员的要求甚至高于开发人员，而通过SQL语句的编写，测试人员可以去进行数据查询、数据正确性完整性验证、构造测试数据、或者行破坏测试或压力测试。因此，作为一个测试人员，掌握SQL的重要性就不言而喻了。在我们的面试过程中，会碰到各种数据库或者编写SQL的面试题。
来看下面这道难倒众生的经典面试题
面试题：行列转换
柠檬班第30期学生要毕业了，他们Linux、MySQL、Java成绩保存在数据表 tb_lemon_grade中，表中字段id，student_name，course，score分别表示成绩id，学生姓名，课程名称，课程成绩，表中数据表1所示。请写出一条SQL，将表1的数据变成表2的形式
id学生姓名课程名称课程成绩
1张三 Linux 85
2张三 MySQL 92
3张三 Java 87
4李四 Linux 96
5李四 MySQL 89
6李四 Java 100
7王五 Linux 91
8王五 MySQL 83
9王五 Java 98
表1
学生姓名LinuxMySQLJava
张三 85 92 87
李四 96 89 100
王五 91 83 98
表2
一：创建表
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');
二：初始化数据
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;
四：使用常量列输出我们的目标结构
可以看到结果已经和我们的图二非常接近了
五：使用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) as 'Linux',
max(CASE COURSE when 'MySQL' THEN SCORE ELSE 0 END) as 'MySQL',
max(CASE COURSE when 'Java' THEN SCORE ELSE 0 END) as 'Java'
FROM tb_lemon_grade
GROUP BY student_name;
结果如下图所示：
使用SUM，结果如下图所示
SELECT student_name,
SUM(CASE COURSE when 'Linux' THEN SCORE ELSE 0 END) as 'Linux',
SUM(CASE COURSE when 'MySQL' THEN SCORE ELSE 0 END) as 'MySQL',
SUM(CASE COURSE when 'Java' THEN SCORE ELSE 0 END) as 'Java'
FROM tb_lemon_grade
GROUP BY student_name;
更多的软件测试视频资料，都在柠檬班的软件测试技术交流群哦~群643368558与群内软测dalao谈笑风生