SQL中的行轉(zhuǎn)列和列轉(zhuǎn)行
導讀
SQL是IT行業(yè)很多崗位都要求具備的一項能力，對于數(shù)據(jù)崗位而言更是如此，甚至說扎實的SQL基礎也往往是入職這些崗位的必備技能。而在SQL面試中，一道出鏡頻率很高的題目就是行轉(zhuǎn)列和列轉(zhuǎn)行的問題，可以說這也是一道經(jīng)典的SQL題目，本文就這一問題做以介紹分享。
給定如下模擬數(shù)據(jù)集，這也是SQL領域經(jīng)典的學生成績表問題。兩張期望的數(shù)據(jù)表分別如下：
1）長表：
scoreLong
2）寬表：
scoreWide
考察的問題就是通過SQL語句實現(xiàn)在這兩種形態(tài)間轉(zhuǎn)換，其中長表轉(zhuǎn)為寬表即行轉(zhuǎn)列，寬表轉(zhuǎn)為長表即列轉(zhuǎn)行。
在行轉(zhuǎn)列中，經(jīng)典的解決方案是條件聚合，即sum+if組合。其基本的思路是這樣的：
在長表的數(shù)據(jù)組織結(jié)構(gòu)中，同一uid對應了多行，即每門課程一條記錄，對應一組分數(shù)，而在寬表中需要將其變成同一uid下僅對應一行
在長表中，僅有一列記錄了課程成績，但在寬表中則每門課作為一列記錄成績
由多行變一行，那么直覺想到的就是要groupby聚合；由一列變多列，那么就涉及到衍生提?。?/span>
既然要用groupby聚合，那么就涉及到將多門課的成績匯總，但現(xiàn)在需要的不是所有成績匯總，而仍然是各門課的獨立成績，所以需要用一個if函數(shù)加以篩選提??；當然，用case when也可以；
在if篩選提取的基礎上，針對不同課程設立不同的提取條件，并最終加一個聚合函數(shù)提取該列成績即可。
按照這一思路，一句SQL實現(xiàn)行轉(zhuǎn)列的寫法如下：
查詢結(jié)果當然是預期的行轉(zhuǎn)列后的結(jié)果：SELECT uid,sum(if(course='語文', score, NULL)) as `語文`,sum(if(course='數(shù)學', score, NULL)) as `數(shù)學`,sum(if(course='英語', score, NULL)) as `英語`,sum(if(course='物理', score, NULL)) as `物理`,sum(if(course='化學', score, NULL)) as `化學`FROM scoreLongGROUP BY uid
其中，if(course='語文', score, NULL)語句實現(xiàn)了當且僅當課程為語文時取值為課程成績，否則取值為空，這相當于衍生了一個新的列字段，且對于每個uid而言，其所有成績就只有特定課程的結(jié)果非空，其余均為空。這樣，無論使用任何聚合函數(shù)，都可以得到該uid下指定課程的成績結(jié)果。這里是用了sum函數(shù)，其實用min、max效果也是一樣的，因為待聚合的數(shù)值中就只有那一個值非空。
列轉(zhuǎn)行是上述過程的逆過程，所以其思路也比較直觀：
行記錄由一行變?yōu)槎嘈?，列字段由多列變?yōu)閱瘟校?/span>
一行變多行需要復制，列字段由多列變單列相當于是堆積的過程，其實也可以看做是復制；
一行變多行，那么復制的最直觀實現(xiàn)當然是使用union，即分別針對每門課程提取一張衍生表，最后將所有課程的衍生表union到一起即可，其中需要注意字段的對齊
按照這一思路，給出SQL實現(xiàn)如下：
SELECT uid, '語文' as course, `語文` as scoreFROM scoreWideWHERE `語文` IS NOT NULLUNIONSELECT uid, '數(shù)學' as course, `數(shù)學` as scoreFROM scoreWideWHERE `數(shù)學` IS NOT NULLUNIONSELECT uid, '英語' as course, `英語` as scoreFROM scoreWideWHERE `英語` IS NOT NULLUNIONSELECT uid, '物理' as course, `物理` as scoreFROM?scoreWideWHERE `物理` IS NOT NULLUNIONSELECT uid, '化學' as course, `化學` as scoreFROM?scoreWideWHERE `化學` IS NOT NULL
在每個單門課的衍生表中，例如這句：SELECT uid, '語文'as course, `語文`as score，用單引號包裹起來的課程名稱是字符串常量，比如語文課的衍生表中的課程名都叫語文，然后將該列命名為course；第二個用反引號包裹起來的課程名實際上是從寬表中引用這一列的取值，然后將其命名為score。
這實際上對應的一個知識點是：在SQL中字符串的引用用單引號（其實雙引號也可以），而列字段名稱的引用則是用反引號
上述用到了where條件過濾成績?yōu)榭罩档挠涗?/span>，這實際是由于在原表中存在有空值的情況，如不加以過濾則在本例中最終查詢記錄有10條，其中兩條記錄的成績字段為空
最后，本例中用union關鍵字實現(xiàn)了多表的縱向拼接，實際上用union all更為合理，二者的區(qū)別是union會完成記錄去重；而union all則簡單的拼接，在確定不存在重復或無需去重的情況下其效率更高。
相關閱讀：