快捷導(dǎo)航

SQL中如何將行轉(zhuǎn)成列詳解

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

行列轉(zhuǎn)換在做報(bào)表分析時(shí)還是經(jīng)常會(huì)遇到的,今天就說一下如何實(shí)現(xiàn)行列轉(zhuǎn)換吧,下面這篇文章主要給大家介紹了關(guān)于SQL中如何將行轉(zhuǎn)成列的相關(guān)資料,需要的朋友可以參考下

SQL中怎么將行轉(zhuǎn)成列？

我們以MySQL數(shù)據(jù)庫(kù)為例，來說明行轉(zhuǎn)列的實(shí)現(xiàn)方式。

首先，假設(shè)我們有一張分?jǐn)?shù)表（tb_score），表中的數(shù)據(jù)如下圖：

然后，我們?cè)賮砜匆幌罗D(zhuǎn)換之后需要得到的結(jié)果，如下圖：

可以看出，這里行轉(zhuǎn)列是將原來的subject字段的多行內(nèi)容選出來，作為結(jié)果集中的不同列，并根據(jù)userid進(jìn)行分組顯示對(duì)應(yīng)的score。通常，我們有兩種方式來實(shí)現(xiàn)這種轉(zhuǎn)換。

1. 使用 CASE…WHEN…THEN 語(yǔ)句實(shí)現(xiàn)行轉(zhuǎn)列，參考如下代碼：

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

注意，SUM() 是為了能夠使用GROUP BY根據(jù)userid進(jìn)行分組，因?yàn)槊恳粋€(gè)userid對(duì)應(yīng)的
subject="語(yǔ)文"的記錄只有一條，所以SUM() 的值就等于對(duì)應(yīng)那一條記錄的score的值。假如userid ='001' and subject='語(yǔ)文' 的記錄有兩條，則此時(shí)SUM() 的值將會(huì)是這兩條記錄的和，同理，使用Max()的值將會(huì)是這兩條記錄里面值最大的一個(gè)。但是正常情況下，一個(gè)user對(duì)應(yīng)一個(gè)subject只有一個(gè)分?jǐn)?shù)，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函數(shù)都可以達(dá)到行轉(zhuǎn)列的效果。

2. 使用 IF() 函數(shù)實(shí)現(xiàn)行轉(zhuǎn)列，參考如下代碼：

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

注意， IF(subject='語(yǔ)文',score,0) 作為條件，即對(duì)所有subject='語(yǔ)文’的記錄的score字段進(jìn)行SUM()、MAX()、MIN()、AVG()操作，如果score沒有值則默認(rèn)為0。

補(bǔ)充：列轉(zhuǎn)行：union

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

行記錄由一行變?yōu)槎嘈校凶侄斡啥嗔凶優(yōu)閱瘟校?/li>
一行變多行需要復(fù)制，列字段由多列變單列相當(dāng)于是堆積的過程，其實(shí)也可以看做是復(fù)制；
一行變多行，那么復(fù)制的最直觀實(shí)現(xiàn)當(dāng)然是使用union，即分別針對(duì)每門課程提取一張衍生表，最后將所有課程的衍生表union到一起即可，其中需要注意字段的對(duì)齊

按照這一思路，給出SQL實(shí)現(xiàn)如下：

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

查詢結(jié)果當(dāng)然是預(yù)期的長(zhǎng)表。這里重點(diǎn)解釋其中的三個(gè)細(xì)節(jié)：

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