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
　　表 2 数据如下：
　　学生姓名
　　Linux
　　MySQL
　　Java
　　张三
　　85
　　92
　　87
　　李四
　　96
　　89
　　100
　　王五
　　91
　　83
　　98一：创建表  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;
　　总结
　　通过上面一步一步的分解，我们就完成了一个行转列的 SQL 语句编写，现在问题又来了，这种方式虽然能达到效果，但是如果课程多了，
　　比如还有英语、数学、物理等等课程，是不是写起来就笔记麻烦了？有没有动态的方式，不管有多少课程，通过 SQL 就能一步到位呢？感兴趣大家可以去尝试下。
　　p s：小编个人收集啦许多软测资料，希望能够帮到学软件测试的朋友们软件资料分享包括但不限于：Java自动化测试、Python自动化测试、性能测试、web测试、APP测试 测试开发工具包：appuim安装包、fiddler安装包（也有配套视频教程）、eclipse、git、jmeter、loadrunner、monkey、postman、soapul、Xmind等等
　　有需要的朋友可以私信小编＂测试＂领取