日常工作中，為了讓數(shù)據(jù)的可讀性更強，經(jīng)常會對數(shù)據(jù)格式進行轉(zhuǎn)化操作。總結(jié)一下日常工作中遇到的關(guān)于行列操作問題。

單行拆分成多行

--創(chuàng)建測試數(shù)據(jù) CREATE TABLE fwj.customer ( id STRING, name STRING, mobiles STRING);INSERT INTO fwj.customer SELECT '1','jim','139,177,158' FROM system.dual;-- 解法一，不建議選擇 SELECT a.id,a.name,substr(a.mobiles,1,3) mobiles FROM fwj.customer a UNION ALL SELECT a.id,a.name,substr(a.mobiles,5,3) mobiles FROM fwj.customer a UNION ALL SELECT a.id,a.name,substr(a.mobiles,9,3) mobiles FROM fwj.customer a -- 解法二 SELECT b.id,a.mobiles,b.name FROM ( SELECT explode(split(t.mobiles,',')) mobiles FROM fwj.customer t )a , fwj.customer b; -- 優(yōu)化，但不行。explode 這類UDTF函數(shù)不支持和其他字段一塊被select。 SELECT t.id,t.name,explode(split(t.mobiles,',')) mobiles FROM fwj.customer t -- 解法三，可以理解成在一次查詢中 -- 先生成了一個視圖 mob 包含了行轉(zhuǎn)列后的數(shù)據(jù)，之后從mob 中取出轉(zhuǎn)換后的數(shù)據(jù)， -- 其他字段仍舊從原表中取。 SELECT a.id,a.name,mob.mobile FROM fwj.customer a lateral view explode(split(a.mobiles,',')) mob AS mobile;

split(str,sep):
該函數(shù)的作用是拆分指定分隔符分割的字符串，返回一個列表。

SELECT split(a.mobiles,',') FROM fwj.customer a;result: ["139","177","158"]

explode(arr):
該函數(shù)是一個表生成函數(shù)。輸入一個列表參數(shù)，將列表中的每個值都轉(zhuǎn)換為一行。

SELECT explode(plit(a.mobiles,',')) mobiles FROM fwj.customer a;result:mobiles 139 177 158

行列互換

測試集

/*創(chuàng)建數(shù)據(jù)庫測試表*/ CREATE TABLE [Scores]([ID] INT IDENTITY(1, 1) PRIMARY KEY ,[Student] VARCHAR(20) ,[Subject] VARCHAR(30) ,[Score] FLOAT)/*插入數(shù)據(jù)庫測試數(shù)據(jù)信息*/INSERT INTO Scores( Student, Subject, Score )VALUES ( 'test001', '語文', '90' )INSERT INTO Scores( Student, Subject, Score )VALUES ( 'test001', '英語', '85' )INSERT INTO Scores( Student, Subject, Score )VALUES ( 'text002', '語文', '90' )INSERT INTO Scores( Student, Subject, Score )VALUES ( 'text002', '英語', '80' )INSERT INTO Scores( Student, Subject, Score )VALUES ( 'test003', '語文', '95' )INSERT INTO Scores( Student, Subject, Score )VALUES ( 'test003', '英語', '85' )

1. case when …then else …end 用法，行列轉(zhuǎn)換

SELECT Student AS '姓名' ,MAX(CASE SubjectWHEN '語文' THEN ScoreELSE 0END) AS '語文' ,--如果這個行是“語文”，就選此行作為列MAX(CASE SubjectWHEN '英語' THEN ScoreELSE 0END) AS '英語' FROM ScoresGROUP BY StudentORDER BY Student

2. pivot(聚合函數(shù)(要轉(zhuǎn)成列值的列名) for 要轉(zhuǎn)換的列 in（目標(biāo)列名）

SELECT Student AS '姓名' ,AVG(語文) AS '語文' ,AVG(英語) AS '英語' FROM Scores PIVOT( AVG(Score) FOR Subject IN ( 語文, 英語 ) )as NewScores GROUP BY Student ORDER BY Student ASC

拓展題

將上表轉(zhuǎn)化為下表的格式

-- 解法一，實用性不如解法二，但可能圖二無法滿足，推薦使用解法二 select t1.item,t1.[time]as startdate,min(t2.[time])as endtime from t1,t1 as t2 where t1.type='start' and t1.type<>t2.type and t2.[time]>t1.[time] group by t1.item,t1.[time]-- 解法二 Select * From T1; With S1 As (Select SeqNo, Item,Type,IIf(Type='Start', Time,Null) sTime,IIf(Type='End', Time,Null) eTime, Lag(Type,1,'0') Over (Partition By Item Order By Time) LastType From T1 ),S2 As ( Select SeqNo,Item,Type,sTime,eTime,LastType,Sum(IIF(Type='Start',1,IIF(Type='End' And LastType='Start',0,1)))Over (Partition By Item Order By SeqNo) Gn From S1 ) Select Item,Gn,Max(sTime) sTime,Max(eTime) eTime From S2 Group By Item,Gn

解法二計算過程：
1.Time 的類型 設(shè)置為 Time(0)
2.S2代碼思路：累加 分組 編號 …，避免萬一 中間 沒有（漏掉了） Start 記錄， 卻有 End 記錄 的情況。如果是 Start 直接加1 ，如果是End 看下 前面是不是 Start ，是 就不加了，如果是第1條記錄， 還是加 1。

總結(jié)

以上是生活随笔為你收集整理的SQL行列问题的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。