偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

老生常談:SQL中的行轉(zhuǎn)列,列轉(zhuǎn)行操作

數(shù)據(jù)庫(kù) SQL Server 數(shù)據(jù)庫(kù)運(yùn)維
本文將為大家介紹的是SQL中的行轉(zhuǎn)列,列轉(zhuǎn)行操作,這也是我們?cè)陂_(kāi)發(fā)的過(guò)程中經(jīng)常遇到的問(wèn)題,希望對(duì)大家有所幫助。
行轉(zhuǎn)列,列轉(zhuǎn)行是我們?cè)陂_(kāi)發(fā)過(guò)程中經(jīng)常碰到的問(wèn)題。行轉(zhuǎn)列一般通過(guò)CASE WHEN 語(yǔ)句來(lái)實(shí)現(xiàn),也可以通過(guò) SQL SERVER 2005 新增的運(yùn)算符PIVOT來(lái)實(shí)現(xiàn)。 用傳統(tǒng)的方法,比較好理解。層次清晰,而且比較習(xí)慣。 但是PIVOT 、UNPIVOT提供的語(yǔ)法比一系列復(fù)雜的 SELECT...CASE 語(yǔ)句中所指定的語(yǔ)法更簡(jiǎn)單、更具可讀性。下面我們通過(guò)幾個(gè)簡(jiǎn)單的例子來(lái)介紹一下列轉(zhuǎn)行、行轉(zhuǎn)列問(wèn)題。

我們首先先通過(guò)一個(gè)老生常談的例子,學(xué)生成績(jī)表(下面簡(jiǎn)化了些)來(lái)形象了解下行轉(zhuǎn)列 

  1. CREATE  TABLE [StudentScores]  
  2. (  
  3.     [UserName]         NVARCHAR(20),        --學(xué)生姓名  
  4.     [Subject]          NVARCHAR(30),        --科目  
  5.     [Score]            FLOAT,               --成績(jī)  
  6. )  
  7.  
  8. INSERT INTO [StudentScores] SELECT 'Nick''語(yǔ)文', 80  
  9. INSERT INTO [StudentScores] SELECT 'Nick''數(shù)學(xué)', 90  
  10. INSERT INTO [StudentScores] SELECT 'Nick''英語(yǔ)', 70  
  11. INSERT INTO [StudentScores] SELECT 'Nick''生物', 85  
  12. INSERT INTO [StudentScores] SELECT 'Kent''語(yǔ)文', 80  
  13. INSERT INTO [StudentScores] SELECT 'Kent''數(shù)學(xué)', 90  
  14. INSERT INTO [StudentScores] SELECT 'Kent''英語(yǔ)', 70  
  15. INSERT INTO [StudentScores] SELECT 'Kent''生物', 85 

如果我想知道每位學(xué)生的每科成績(jī),而且每個(gè)學(xué)生的全部成績(jī)排成一行,這樣方便我查看、統(tǒng)計(jì),導(dǎo)出數(shù)據(jù)

  1. SELECT   
  2.       UserName,   
  3.       MAX(CASE Subject WHEN '語(yǔ)文' THEN Score ELSE 0 ENDAS '語(yǔ)文',  
  4.       MAX(CASE Subject WHEN '數(shù)學(xué)' THEN Score ELSE 0 ENDAS '數(shù)學(xué)',  
  5.       MAX(CASE Subject WHEN '英語(yǔ)' THEN Score ELSE 0 ENDAS '英語(yǔ)',  
  6.       MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 ENDAS '生物' 
  7. FROM dbo.[StudentScores]  
  8. GROUP BY UserName 

查詢(xún)結(jié)果如圖所示,這樣我們就能很清楚的了解每位學(xué)生所有的成績(jī)了

 

接下來(lái)我們來(lái)看看第二個(gè)小列子。有一個(gè)游戲玩家充值表(僅僅為了說(shuō)明,舉的一個(gè)小例子),

  1. CREATE TABLE [Inpours]  
  2. (  
  3.     [ID]             INT IDENTITY(1,1),   
  4.     [UserName]          NVARCHAR(20),  --游戲玩家  
  5.     [CreateTime]     DATETIME,      --充值時(shí)間      
  6.     [PayType]         NVARCHAR(20),  --充值類(lèi)型      
  7.     [Money]             DECIMAL,       --充值金額  
  8.     [IsSuccess]         BIT,           --是否成功 1表示成功, 0表示失敗  
  9.     CONSTRAINT [PK_Inpours_ID] PRIMARY KEY(ID)  
  10. )  
  11.  
  12. INSERT INTO Inpours SELECT '張三''2010-05-01''支付寶', 50, 1  
  13. INSERT INTO Inpours SELECT '張三''2010-06-14''支付寶', 50, 1  
  14. INSERT INTO Inpours SELECT '張三''2010-06-14''手機(jī)短信', 100, 1  
  15. INSERT INTO Inpours SELECT '李四''2010-06-14''手機(jī)短信', 100, 1  
  16. INSERT INTO Inpours SELECT '李四''2010-07-14''支付寶', 100, 1  
  17. INSERT INTO Inpours SELECT '王五''2010-07-14''工商銀行卡', 100, 1  
  18. INSERT INTO Inpours SELECT '趙六''2010-07-14''建設(shè)銀行卡', 100, 1 

下面來(lái)了一個(gè)統(tǒng)計(jì)數(shù)據(jù)的需求,要求按日期、支付方式來(lái)統(tǒng)計(jì)充值金額信息。這也是一個(gè)典型的行轉(zhuǎn)列的例子。我們可以通過(guò)下面的腳本來(lái)達(dá)到目的

  1. SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,  
  2.        CASE PayType WHEN '支付寶'     THEN SUM(Money) ELSE 0 END AS '支付寶',  
  3.        CASE PayType WHEN '手機(jī)短信'    THEN SUM(Money) ELSE 0 END AS '手機(jī)短信',  
  4.        CASE PayType WHEN '工商銀行卡'  THEN SUM(Money) ELSE 0 END AS '工商銀行卡',  
  5.        CASE PayType WHEN '建設(shè)銀行卡'  THEN SUM(Money) ELSE 0 END AS '建設(shè)銀行卡' 
  6. FROM Inpours  
  7. GROUP BY CreateTime, PayType 

如圖所示,我們這樣只是得到了這樣的輸出結(jié)果,還需進(jìn)一步處理,才能得到想要的結(jié)果

  1. SELECT   
  2.        CreateTime,   
  3.        ISNULL(SUM([支付寶]), 0) AS [支付寶],   
  4.        ISNULL(SUM([手機(jī)短信]), 0) AS [手機(jī)短信],  
  5.        ISNULL(SUM([工商銀行卡]), 0) AS [工商銀行卡],   
  6.        ISNULL(SUM([建設(shè)銀行卡]), 0) AS [建設(shè)銀行卡]  
  7. FROM 
  8. (  
  9.     SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,  
  10.            CASE PayType WHEN '支付寶'     THEN SUM(Money) ELSE 0 END AS '支付寶',  
  11.            CASE PayType WHEN '手機(jī)短信'   THEN SUM(Money) ELSE 0 END AS '手機(jī)短信',  
  12.            CASE PayType WHEN '工商銀行卡' THEN SUM(Money) ELSE 0 END AS '工商銀行卡',  
  13.            CASE PayType WHEN '建設(shè)銀行卡' THEN SUM(Money) ELSE 0 END AS '建設(shè)銀行卡' 
  14.     FROM Inpours  
  15.     GROUP BY CreateTime, PayType  
  16. ) T  
  17. GROUP BY CreateTime 

其實(shí)行轉(zhuǎn)列,關(guān)鍵是要理清邏輯,而且對(duì)分組(Group by)概念比較清晰。上面兩個(gè)列子基本上就是行轉(zhuǎn)列的類(lèi)型了。但是有個(gè)問(wèn)題來(lái)了,上面是我為了說(shuō)明弄的一個(gè)簡(jiǎn)單列子。實(shí)際中,可能支付方式特別多,而且邏輯也復(fù)雜很多,可能涉及匯率、手續(xù)費(fèi)等等(曾經(jīng)做個(gè)這樣一個(gè)),如果支付方式特別多,我們的CASE WHEN 會(huì)弄出一大堆,確實(shí)比較惱火,而且新增一種支付方式,我們還得修改腳本如果把上面的腳本用動(dòng)態(tài)SQL改寫(xiě)一下,我們就能輕松解決這個(gè)問(wèn)題 

  1. DECLARE @cmdText    VARCHAR(8000);  
  2. DECLARE @tmpSql        VARCHAR(8000);  
  3.  
  4. SET @cmdText = 'SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,' + CHAR(10);  
  5. SELECT @cmdText = @cmdText + ' CASE PayType WHEN ''' + PayType + ''' THEN SUM(Money) ELSE 0 END AS ''' + PayType   
  6.                 + ''',' + CHAR(10)  FROM (SELECT DISTINCT PayType FROM Inpours ) T  
  7.  
  8. SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意這里,如果沒(méi)有加CHAR(10) 則用LEFT(@cmdText, LEN(@cmdText) -1)  
  9.  
  10. SET @cmdText = @cmdText + ' FROM Inpours     GROUP BY CreateTime, PayType ';  
  11.  
  12. SET @tmpSql ='SELECT CreateTime,' + CHAR(10);  
  13. SELECT @tmpSql = @tmpSql + ' ISNULL(SUM(' + PayType  + '), 0) AS ''' + PayType  + ''','  + CHAR(10)  
  14.                     FROM  (SELECT DISTINCT PayType FROM Inpours ) T  
  15.  
  16. SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ' FROM (' + CHAR(10);  
  17.  
  18. SET @cmdText = @tmpSql + @cmdText + ') T GROUP BY CreateTime ';  
  19. PRINT @cmdText  
  20. EXECUTE (@cmdText); 

下面是通過(guò)PIVOT來(lái)進(jìn)行行轉(zhuǎn)列的用法,大家可以對(duì)比一下,確實(shí)要簡(jiǎn)單、更具可讀性(呵呵,習(xí)慣的前提下)

  1. SELECT   
  2.         CreateTime, [支付寶] , [手機(jī)短信],  
  3.         [工商銀行卡] , [建設(shè)銀行卡]  
  4. FROM 
  5. (  
  6.     SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType, Money  
  7.     FROM Inpours  
  8. ) P  
  9. PIVOT (  
  10.             SUM(Money)  
  11.             FOR PayType IN 
  12.             ([支付寶], [手機(jī)短信], [工商銀行卡], [建設(shè)銀行卡])  
  13.       ) AS T  
  14. ORDER BY CreateTime 

有時(shí)可能會(huì)出現(xiàn)這樣的錯(cuò)誤:

消息 325,級(jí)別 15,狀態(tài) 1,第 9 行

'PIVOT' 附近有語(yǔ)法錯(cuò)誤。您可能需要將當(dāng)前數(shù)據(jù)庫(kù)的兼容級(jí)別設(shè)置為更高的值,以啟用此功能。有關(guān)存儲(chǔ)過(guò)程 sp_dbcmptlevel 的信息,請(qǐng)參見(jiàn)幫助。

這個(gè)是因?yàn)椋簩?duì)升級(jí)到 SQL Server 2005 或更高版本的數(shù)據(jù)庫(kù)使用 PIVOT 和 UNPIVOT 時(shí),必須將數(shù)據(jù)庫(kù)的兼容級(jí)別設(shè)置為 90 或更高。有關(guān)如何設(shè)置數(shù)據(jù)庫(kù)兼容級(jí)別的信息,請(qǐng)參閱 sp_dbcmptlevel (Transact-SQL)。 例如,只需在執(zhí)行上面腳本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了, Test 是所在數(shù)據(jù)庫(kù)的名稱(chēng)。

下面我們來(lái)看看列轉(zhuǎn)行,主要是通過(guò)UNION ALL ,MAX來(lái)實(shí)現(xiàn)。假如有下面這么一個(gè)表

  1. CREATE TABLE ProgrectDetail  
  2. (  
  3.     ProgrectName         NVARCHAR(20), --工程名稱(chēng)  
  4.     OverseaSupply        INT,          --海外供應(yīng)商供給數(shù)量  
  5.     NativeSupply         INT,          --國(guó)內(nèi)供應(yīng)商供給數(shù)量  
  6.     SouthSupply          INT,          --南方供應(yīng)商供給數(shù)量  
  7.     NorthSupply          INT           --北方供應(yīng)商供給數(shù)量  
  8. )  
  9.  
  10. INSERT INTO ProgrectDetail  
  11. SELECT 'A', 100, 200, 50, 50  
  12. UNION ALL 
  13. SELECT 'B', 200, 300, 150, 150  
  14. UNION ALL 
  15. SELECT 'C', 159, 400, 20, 320  
  16. UNION ALL 
  17. SELECT 'D', 250, 30, 15, 15 

我們可以通過(guò)下面的腳本來(lái)實(shí)現(xiàn),查詢(xún)結(jié)果如下圖所示

  1. SELECT ProgrectName, 'OverseaSupply' AS Supplier,  
  2.         MAX(OverseaSupply) AS 'SupplyNum' 
  3. FROM ProgrectDetail  
  4. GROUP BY ProgrectName  
  5. UNION ALL 
  6. SELECT ProgrectName, 'NativeSupply' AS Supplier,  
  7.         MAX(NativeSupply) AS 'SupplyNum' 
  8. FROM ProgrectDetail  
  9. GROUP BY ProgrectName  
  10. UNION ALL 
  11. SELECT ProgrectName, 'SouthSupply' AS Supplier,  
  12.         MAX(SouthSupply) AS 'SupplyNum' 
  13. FROM ProgrectDetail  
  14. GROUP BY ProgrectName  
  15. UNION ALL 
  16. SELECT ProgrectName, 'NorthSupply' AS Supplier,  
  17.         MAX(NorthSupply) AS 'SupplyNum' 
  18. FROM ProgrectDetail  
  19. GROUP BY ProgrectName 

 

用UNPIVOT 實(shí)現(xiàn)如下:

  1. SELECT ProgrectName,Supplier,SupplyNum  
  2. FROM   
  3. (  
  4.     SELECT ProgrectName, OverseaSupply, NativeSupply,  
  5.            SouthSupply, NorthSupply  
  6.      FROM ProgrectDetail  
  7. )T  
  8. UNPIVOT   
  9. (  
  10.     SupplyNum FOR Supplier IN 
  11.     (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )  
  12. ) P 

原文標(biāo)題:重溫SQL——行轉(zhuǎn)列,列轉(zhuǎn)行

鏈接:http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html

【編輯推薦】
  1. SQL Server數(shù)據(jù)庫(kù)和Oracle行轉(zhuǎn)列的特殊方案描述
  2. SQL Server行轉(zhuǎn)列的什么情況下被用?
  3. SQL Server實(shí)例中對(duì)另個(gè)實(shí)例的調(diào)用
  4. SQL Server identity列,美中不足之處
  5. SQL Server DateTime數(shù)據(jù)類(lèi)型的另類(lèi)解讀

 

責(zé)任編輯:彭凡 來(lái)源: 博客園
相關(guān)推薦

2021-06-23 10:13:00

SQL行轉(zhuǎn)列列轉(zhuǎn)行

2021-11-08 23:08:06

SQL Serve數(shù)據(jù)庫(kù)開(kāi)發(fā)

2015-06-25 10:46:23

數(shù)據(jù)中心節(jié)能

2015-07-21 13:39:58

Javascript作用域

2015-04-08 11:50:07

數(shù)據(jù)加密數(shù)據(jù)泄露

2022-03-08 15:01:48

負(fù)載均衡IP服務(wù)器

2014-07-25 13:34:08

2011-08-18 14:47:06

2012-02-13 09:46:56

數(shù)據(jù)中心耗能服務(wù)器虛擬化

2014-08-21 10:25:44

網(wǎng)絡(luò)安全兒童賬戶(hù)Google

2019-08-22 17:19:19

javascript去重數(shù)組

2024-01-02 09:42:17

C#開(kāi)發(fā)Windows消息循環(huán)機(jī)制

2017-04-11 13:54:49

HTTPURLHTML

2021-04-12 09:36:25

Redis擊穿穿透

2021-04-12 09:58:46

Redis數(shù)據(jù)庫(kù)代碼

2010-01-28 13:55:45

三層交換機(jī)

2020-01-14 10:37:38

存儲(chǔ)DateTime數(shù)值

2010-03-29 09:04:51

VB.NET

2025-05-06 00:51:00

2024-10-16 21:17:59

點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)