SQL經(jīng)典面試題 - 行列轉(zhuǎn)換

在我們的實(shí)際工作中，為了研發(fā)出功能完整、系統(tǒng)健壯的軟件，需要我們測(cè)試人員想法設(shè)法的去挑出軟件的問(wèn)題，所以很多公司對(duì)測(cè)試人員的要求甚至高于開(kāi)發(fā)人員，而通過(guò)SQL語(yǔ)句的編寫(xiě)，測(cè)試人員可以去進(jìn)行數(shù)據(jù)查詢(xún)、數(shù)據(jù)正確性完整性驗(yàn)證、構(gòu)造測(cè)試數(shù)據(jù)、或者行破壞測(cè)試或壓力測(cè)試。因此，作為一個(gè)測(cè)試人員，掌握SQL的重要性就不言而喻了。在我們的面試過(guò)程中，會(huì)碰到各種數(shù)據(jù)庫(kù)或者編寫(xiě)SQL的面試題。
來(lái)看下面這道難倒眾生的經(jīng)典面試題
面試題：行列轉(zhuǎn)換
檸檬班第30期學(xué)生要畢業(yè)了，他們Linux、MySQL、Java成績(jī)保存在數(shù)據(jù)表 tb_lemon_grade中，表中字段id，student_name，course，score分別表示成績(jī)id，學(xué)生姓名，課程名稱(chēng)，課程成績(jī)，表中數(shù)據(jù)表1所示。請(qǐng)寫(xiě)出一條SQL，將表1的數(shù)據(jù)變成表2的形式
id    學(xué)生姓名    課程名稱(chēng)    課程成績(jī)
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
學(xué)生姓名    Linux    MySQL    Java
張三          85      92     87
李四          96      89     100
王五          91      83     98
表2
一：創(chuàng)建表
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');
二：初始化數(shù)據(jù)
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);
三：首先我們查詢(xún)出所有數(shù)據(jù),這個(gè)結(jié)果和我們的圖1是一樣的
select * from  tb_lemon_grade;

四：使用常量列輸出我們的目標(biāo)結(jié)構(gòu)
可以看到結(jié)果已經(jīng)和我們的圖二非常接近了

五：使用IF函數(shù)，替換我們的常量列，將成績(jī)賦值到對(duì)應(yīng)行的對(duì)應(yīng)列
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;
運(yùn)行SQL，結(jié)果如下所示：

六：我們來(lái)分析這個(gè)結(jié)果集，
在原始結(jié)構(gòu)中，每一行表示了某個(gè)同學(xué)某一個(gè)科的成績(jī)，以第一行為例，第一行是張三同學(xué)Linux的成績(jī)，所以我們結(jié)果集中Linux有成績(jī)?yōu)?5，而其他兩列MySQL和Java作為常量列，成績(jī)?yōu)?。
再分析每個(gè)同學(xué)的成績(jī)的所有行，如下圖所示，每個(gè)方塊內(nèi)包含行中，就有該同學(xué)這門(mén)課程的成績(jī)，并且該方塊內(nèi)其余行的成績(jī)值為0。因此，不難想到，我們可以使用分組，通過(guò)分組提取出每科的成績(jī)

七：分組，使用MAX函數(shù)取出最大值
（因?yàn)槠渲兄挥幸恍谐煽?jī)?yōu)檎鎸?shí)成績(jī)，其他行值為0，所以最大值就是真實(shí)成績(jī)）
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;

八：也可以分組后，對(duì)每行數(shù)據(jù)進(jìn)行求和，使用SUM函數(shù)，語(yǔ)句和結(jié)果如下：
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語(yǔ)句可以達(dá)到效果，那使用CASE語(yǔ)句也是同樣的效果
分組，使用MAX聚合函數(shù)
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;
結(jié)果如下圖所示：

使用SUM，結(jié)果如下圖所示
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;

總結(jié)：
通過(guò)上面一步一步的分解，我們就完成了一個(gè)行轉(zhuǎn)列的SQL語(yǔ)句編寫(xiě)，現(xiàn)在問(wèn)題又來(lái)了，這種方式雖然能達(dá)到效果，但是如果課程多了，比如還有英語(yǔ)、數(shù)學(xué)、物理等等課程，是不是寫(xiě)起來(lái)就筆記麻煩了？有沒(méi)有動(dòng)態(tài)的方式，不管有多少課程，通過(guò)SQL就能一步到位呢？大家可以去嘗試下，