SQL中如何將行轉成列詳解

更新時間：2022年11月02日 08:54:15 作者：流楚丶格念

行列轉換在做報表分析時還是經常會遇到的,今天就說一下如何實現行列轉換吧,下面這篇文章主要給大家介紹了關于SQL中如何將行轉成列的相關資料,需要的朋友可以參考下

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

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

行記錄由一行變?yōu)槎嘈?，列字段由多列變?yōu)閱瘟校?/li>
一行變多行需要復制，列字段由多列變單列相當于是堆積的過程，其實也可以看做是復制；
一行變多行，那么復制的最直觀實現當然是使用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

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

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