在我们的实际工作中,为了研发出功能完整、系统健壮的软件,需要我们测试人员想法设法的去挑出软件的问题,所以很多公司对测试人员的要求甚至高于开发人员,而通过SQL语句的编写,测试人员可以去进行数据查询、数据正确性完整性验证、构造测试数据、或者行破坏测试或压力测试。因此,作为一个测试人员,掌握SQL的重要性就不言而喻了。在我们的面试过程中,会碰到各种数据库或者编写SQL的面试题。面试题:行列转换 来看下面这道难倒众生的经典面试题 柠檬班第30期学生要毕业了,他们Linux、MySQL、Java成绩保存在数据表tblemongrade中,表中字段id,studentname,course,score分别表示成绩id,学生姓名,课程名称,课程成绩,表中数据表1所示。请写出一条SQL,将表1的数据变成表2的形式 表一数据如下: id 学生姓名 课程名称 课程成绩 1hr张三 Linux 85hr2hr张三 MySQL 92hr3hr张三 Java 87hr4hr李四 Linux 96hr5hr李四 MySQL 89hr6hr李四 Java 100hr7hr王五 Linux 91hr8hr王五 MySQL 83hr9hr王五 Java 98hr表2数据如下: 学生姓名 Linux MySQL Java 张三 85hr92hr87hr李四 96hr89hr100hr王五 91hr83hr98一:创建表CREATETABLEtblemongrade(idINT(10)NOTNULLAUTOINCREMENTPRIMARYKEY,studentnameVARCHAR(20)DEFAULTNULL,courseVARCHAR(20)DEFAULTNULL,scoreFLOATDEFAULT0);二:初始化数据INSERTINTOtblemongrade(studentname,course,score)VALUES(张三,Linux,85),(张三,MySQL,92),(张三,Java,87),(李四,Linux,96),(李四,MySQL,89),(李四,Java,100),(王五,Linux,91),(王五,MySQL,83),(王五,Java,98);三:首先我们查询出所有数据,这个结果和我们的图1是一样的 四:使用常量列输出我们的目标结构 可以看到结果已经和我们的图二非常接近了 五:使用IF函数,替换我们的常量列,将成绩赋值到对应行的对应列SELECTstudentname,IF(COURSELinux,SCORE,0)Linux,IF(COURSEMySQL,SCORE,0)MySQL,IF(COURSEJava,SCORE,0)JavaFROM 运行SQL,结果如下所示: 六:我们来分析这个结果集, 在原始结构中,每一行表示了某个同学某一个科的成绩,以第一行为例,第一行是张三同学Linux的成绩,所以我们结果集中Linux有成绩为85,而其他两列MySQL和Java作为常量列,成绩为0。 再分析每个同学的成绩的所有行,如下图所示,每个方块内包含行中,就有该同学这门课程的成绩,并且该方块内其余行的成绩值为0。因此,不难想到,我们可以使用分组,通过分组提取出每科的成绩七:分组,使用MAX函数取出最大值 (因为其中只有一行成绩为真实成绩,其他行值为0,所以最大值就是真实成绩)SELECTstudentname,MAX(IF(COURSELinux,SCORE,0))Linux,MAX(IF(COURSEMySQL,SCORE,0))MySQL,MAX(IF(COURSEJava,SCORE,0))JavaFROMtblemongradeGROUPBY 八:也可以分组后,对每行数据进行求和,使用SUM函数,语句和结果如下:SELECTstudentname,SUM(IF(COURSELinux,SCORE,0))Linux,SUM(IF(COURSEMySQL,SCORE,0))MySQL,SUM(IF(COURSEJava,SCORE,0))JavaFROMtblemongradeGROUPBY 九:既然使用IF语句可以达到效果,那使用CASE语句也是同样的效果 分组,使用MAX聚合函数SELECTstudentname,max(CASECOURSEwhenLinuxTHENSCOREELSE0END)asLinux,max(CASECOURSEwhenMySQLTHENSCOREELSE0END)asMySQL,max(CASECOURSEwhenJavaTHENSCOREELSE0END)asJavaFROMtblemongradeGROUPBY 结果如下图所示: 使用SUM,结果如下图所示SELECTstudentname,SUM(CASECOURSEwhenLinuxTHENSCOREELSE0END)asLinux,SUM(CASECOURSEwhenMySQLTHENSCOREELSE0END)asMySQL,SUM(CASECOURSEwhenJavaTHENSCOREELSE0END)asJavaFROMtblemongradeGROUPBY 总结 通过上面一步一步的分解,我们就完成了一个行转列的SQL语句编写,现在问题又来了,这种方式虽然能达到效果,但是如果课程多了, 比如还有英语、数学、物理等等课程,是不是写起来就笔记麻烦了?有没有动态的方式,不管有多少课程,通过SQL就能一步到位呢?感兴趣大家可以去尝试下。 ps:小编个人收集啦许多软测资料,希望能够帮到学软件测试的朋友们软件资料分享包括但不限于:Java自动化测试、Python自动化测试、性能测试、web测试、APP测试测试开发工具包:appuim安装包、fiddler安装包(也有配套视频教程)、eclipse、git、jmeter、loadrunner、monkey、postman、soapul、Xmind等等 有需要的朋友可以私信小编测试领取