SQL中如何將行轉成列詳解

2022-11-03 14:02:40

SQL中怎麼將行轉成列？

我們以MySQL資料庫為例，來說明行轉列的實現方式。

首先，假設我們有一張分數表（tb_score），表中的資料如下圖：

然後，我們再來看一下轉換之後需要得到的結果，如下圖：

可以看出，這裡行轉列是將原來的subject欄位的多行內容選出來，作為結果集中的不同列，並根據userid進行分組顯示對應的score。通常，我們有兩種方式來實現這種轉換。

1. 使用 CASE…WHEN…THEN 語句實現行轉列，參考如下程式碼：

SELECT userid, 
SUM(CASE `subject` WHEN '語文' THEN score ELSE 0 END) as '語文', 
SUM(CASE `subject` WHEN '數學' THEN score ELSE 0 END) as '數學', 
SUM(CASE `subject` WHEN '英語' THEN score ELSE 0 END) as '英語', 
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
FROM tb_score 
GROUP BY userid

注意，SUM() 是為了能夠使用GROUP BY根據userid進行分組，因為每一個userid對應的
subject="語文"的記錄只有一條，所以SUM() 的值就等於對應那一條記錄的score的值。假如userid ='001' and subject='語文' 的記錄有兩條，則此時SUM() 的值將會是這兩條記錄的和，同理，使用Max()的值將會是這兩條記錄裡面值最大的一個。但是正常情況下，一個user對應一個subject只有一個分數，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函數都可以達到行轉列的效果。

2. 使用 IF() 函數實現行轉列，參考如下程式碼：

SELECT userid, 
SUM(IF(`subject`='語文',score,0)) as '語文', 
SUM(IF(`subject`='數學',score,0)) as '數學', 
SUM(IF(`subject`='英語',score,0)) as '英語', 
SUM(IF(`subject`='政治',score,0)) as '政治' 
FROM tb_score 
GROUP BY userid

注意， IF(subject='語文',score,0) 作為條件，即對所有subject='語文’的記錄的score欄位進行SUM()、MAX()、MIN()、AVG()操作，如果score沒有值則預設為0。

補充：列轉行：union

列轉行是上述過程的逆過程，所以其思路也比較直觀：

行記錄由一行變為多行，列欄位由多列變為單列；
一行變多行需要複製，列欄位由多列變單列相當於是堆積的過程，其實也可以看做是複製；
一行變多行，那麼複製的最直觀實現當然是使用union，即分別針對每門課程提取一張衍生表，最後將所有課程的衍生表union到一起即可，其中需要注意欄位的對齊

按照這一思路，給出SQL實現如下：

SELECT uid,      
		sum(if(course='語文', score, NULL)) as `語文`,      				sum(if(course='數學', score, NULL)) as `數學`,      				sum(if(course='英語', score, NULL)) as `英語`,      				sum(if(course='物理', score, NULL)) as `物理`,      
		sum(if(course='化學', score, NULL)) as `化學`FROM scoreLongGROUP BY uid

查詢結果當然是預期的長表。這裡重點解釋其中的三個細節：

在每個單門課的衍生表中，例如這句：SELECT uid, ‘語文’ as course, 語文 as score，用單引號包裹起來的課程名稱是字串常數，比如語文課的衍生表中的課程名都叫語文，然後將該列命名為course；第二個用反引號包裹起來的課程名實際上是從寬表中參照這一列的取值，然後將其命名為score。

這實際上對應的一個知識點是：在SQL中字串的參照用單引號（其實雙引號也可以），而列欄位名稱的參照則是用反引號.

上述用到了where條件過濾成績為空值的記錄，這實際是由於在原表中存在有空值的情況，如不加以過濾則在本例中最終查詢記錄有10條，其中兩條記錄的成績欄位為空

最後，本例中用union關鍵字實現了多表的縱向拼接，實際上用union all更為合理，二者的區別是union會完成記錄去重；而union all則簡單的拼接，在確定不存在重複或無需去重的情況下其效率更高。

總結

到此這篇關於SQL中如何將行轉成列的文章就介紹到這了,更多相關SQL將行轉成列內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

SQL中如何將行轉成列詳解

目錄

SQL中怎麼將行轉成列？

1. 使用 CASE…WHEN…THEN 語句實現行轉列，參考如下程式碼：

2. 使用 IF() 函數實現行轉列，參考如下程式碼：

補充：列轉行：union

總結

熱門文章