久久中文娱乐网,99er在线,日韩精品免费一区二区无码视频,大香蕉福利资源视频,中国婬乱a一级毛多女,爱搞在线观看,一级黄色性爱视频免费看,亚洲激情偷拍

點(diǎn)擊關(guān)注上方“SQL數(shù)據(jù)庫開發(fā)”，

設(shè)為“置頂或星標(biāo)”，第一時(shí)間送達(dá)干貨

行轉(zhuǎn)列，列轉(zhuǎn)行是我們在開發(fā)過程中經(jīng)常碰到的問題。行轉(zhuǎn)列一般通過CASE WHEN 語句來實(shí)現(xiàn)，也可以通過 SQL SERVER 的運(yùn)算符PIVOT來實(shí)現(xiàn)。用傳統(tǒng)的方法，比較好理解。層次清晰，而且比較習(xí)慣。但是PIVOT 、UNPIVOT提供的語法比一系列復(fù)雜的SELECT…CASE 語句中所指定的語法更簡單、更具可讀性。下面我們通過幾個(gè)簡單的例子來介紹一下列轉(zhuǎn)行、行轉(zhuǎn)列問題。

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

CREATE ?TABLE [StudentScores] ( ? [UserName] ? ? ? ? NVARCHAR(20), ? ? ? ?--學(xué)生姓名 ? ?[Subject] ? ? ? ? ?NVARCHAR(30), ? ? ? ?--科目 ? ?[Score] ? ? ? ? ? ?FLOAT, ? ? ? ? ? ? ? --成績 ) INSERT INTO [StudentScores] SELECT 'Nick', '語文', 80 INSERT INTO [StudentScores] SELECT 'Nick', '數(shù)學(xué)', 90 INSERT INTO [StudentScores] SELECT 'Nick', '英語', 70 INSERT INTO [StudentScores] SELECT 'Nick', '生物', 85 INSERT INTO [StudentScores] SELECT 'Kent', '語文', 80 INSERT INTO [StudentScores] SELECT 'Kent', '數(shù)學(xué)', 90 INSERT INTO [StudentScores] SELECT 'Kent', '英語', 70 INSERT INTO [StudentScores] SELECT 'Kent', '生物', 85

(提示：可以左右滑動代碼)

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

SELECT ? ? ?UserName, ? ? ?MAX(CASE Subject WHEN '語文' THEN Score ELSE 0 END) AS '語文', ? ? ?MAX(CASE Subject WHEN '數(shù)學(xué)' THEN Score ELSE 0 END) AS '數(shù)學(xué)', ? ? ?MAX(CASE Subject WHEN '英語' THEN Score ELSE 0 END) AS '英語', ? ? ?MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物' FROM dbo.[StudentScores] GROUP BY UserName

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

接下來我們來看看第二個(gè)小列子。有一個(gè)游戲玩家充值表（僅僅為了說明，舉的一個(gè)小例子），

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

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

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

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

SELECT
 ? ? ? CreateTime,
 ? ? ? ISNULL(SUM([支付寶]) ? ?, 0) ?AS [支付寶],
 ? ? ? ISNULL(SUM([手機(jī)短信]) ?, 0) ?AS [手機(jī)短信],
 ? ? ? ISNULL(SUM([工商銀行卡]), 0) ?AS [工商銀行卡], ?
 ? ? ? ISNULL(SUM([建設(shè)銀行卡]), 0) ?AS [建設(shè)銀行卡]
FROM
(
 ? ?SELECT 
 ? ? ? ? ? CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
 ? ? ? ? ? CASE PayType WHEN '支付寶' ? ? THEN SUM(Money) ELSE 0 END AS '支付寶' ,
 ? ? ? ? ? CASE PayType WHEN '手機(jī)短信' ? THEN SUM(Money) ELSE 0 END AS '手機(jī)短信',
 ? ? ? ? ? CASE PayType WHEN '工商銀行卡' THEN SUM(Money) ELSE 0 END AS '工商銀行卡',
 ? ? ? ? ? CASE PayType WHEN '建設(shè)銀行卡' THEN SUM(Money) ELSE 0 END AS '建設(shè)銀行卡'
 ? ?FROM Inpours
 ? ?GROUP BY CreateTime, PayType
) T
GROUP BY CreateTime

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

DECLARE @cmdText ? ?VARCHAR(8000);
DECLARE @tmpSql ? ? ? ?VARCHAR(8000);
SET @cmdText = 'SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,' + CHAR(10);
SELECT @cmdText = @cmdText + ' CASE PayType WHEN ''' +
 PayType + ''' THEN SUM(Money) ELSE 0 END AS ''' + PayType
 ? ? ? + ''',' + CHAR(10) ?FROM (SELECT DISTINCT PayType FROM Inpours ) T

SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意這里，如果沒有加CHAR(10) 則用LEFT(@cmdText, LEN(@cmdText) -1)

SET @cmdText = @cmdText + ' FROM Inpours ? ?
 ? ?GROUP BY CreateTime, PayType ';

SET @tmpSql ='SELECT CreateTime,' + CHAR(10);
SELECT @tmpSql = @tmpSql + ' ISNULL(SUM(' + PayType ?+ '), 0) AS ''' +
PayType ?+ ''',' ?+ CHAR(10)
FROM ?(SELECT DISTINCT PayType FROM Inpours ) T

SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ' FROM (' + CHAR(10);

SET @cmdText = @tmpSql + @cmdText + ') T GROUP BY CreateTime ';
PRINT @cmdText
EXECUTE (@cmdText);

下面是通過PIVOT來進(jìn)行行轉(zhuǎn)列的用法，大家可以對比一下，確實(shí)要簡單、更具可讀性

SELECT CreateTime, [支付寶] , [手機(jī)短信],[工商銀行卡] , [建設(shè)銀行卡]
FROM
(
 ? ?SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType, Money
 ? ?FROM Inpours
) P
PIVOT (
 ? ? ? ? ? ?SUM(Money)
 ? ? ? ? ? ?FOR PayType IN
 ? ? ? ? ? ?([支付寶], [手機(jī)短信], [工商銀行卡], [建設(shè)銀行卡])
 ? ? ?) AS T
ORDER BY CreateTime

有時(shí)可能會出現(xiàn)這樣的錯誤：

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

‘PIVOT’ 附近有語法錯誤。您可能需要將當(dāng)前數(shù)據(jù)庫的兼容級別設(shè)置為更高的值，以啟用此功能。有關(guān)存儲過程 sp_dbcmptlevel 的信息，請參見幫助。

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

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

Create Table ProgrectDetail
(
 ? ?ProgrectName ? ? ? ? NVARCHAR(20), --工程名稱
 ? ?OverseaSupply ? ? ? ?INT, ? ? ? ? ?--海外供應(yīng)商供給數(shù)量
 ? ?NativeSupply ? ? ? ? INT, ? ? ? ? ?--國內(nèi)供應(yīng)商供給數(shù)量
 ? ?SouthSupply ? ? ? ? ?INT, ? ? ? ? ?--南方供應(yīng)商供給數(shù)量
 ? ?NorthSupply ? ? ? ? ?INT ? ? ? ? ? --北方供應(yīng)商供給數(shù)量
)

INSERT INTO ProgrectDetail
SELECT 'A', 100, 200, 50, 50
UNION ALL
SELECT 'B', 200, 300, 150, 150
UNION ALL
SELECT 'C', 159, 400, 20, 320
UNION ALL
SELECT 'D', 250, 30, 15, 15

我們可以通過下面的腳本來實(shí)現(xiàn)，查詢結(jié)果如下圖所示

SELECT ?ProgrectName, 'OverseaSupply' AS Supplier,
 ? ? ? MAX(OverseaSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, 'NativeSupply' AS Supplier,
 ? ? ? ?MAX(NativeSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, 'SouthSupply' AS Supplier,
 ? ? ? ?MAX(SouthSupply) AS 'SupplyNum'
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, 'NorthSupply' AS Supplier,
 ? ? ? ?MAX(NorthSupply) AS 'SupplyNum' 
FROM ProgrectDetail
GROUP BY ProgrectName

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

SELECT ProgrectName,Supplier,SupplyNum
FROM ?
(
 ? ?SELECT ProgrectName, OverseaSupply, NativeSupply,
 ? ? ? ? ? SouthSupply, NorthSupply
 ? ? FROM ProgrectDetail
)T
UNPIVOT ?
(
 ? ?SupplyNum FOR Supplier IN 
 ? ?(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P

作者：瀟湘隱者

www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html

——End——
后臺回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。
推薦閱讀
百度網(wǎng)盤突然大調(diào)整，網(wǎng)友炸了..
大公司病，太形象了
阿里規(guī)定超過3張表，禁止JOIN，為何？
MySQL 常用命令手冊
5個(gè)免費(fèi)在線 SQL 數(shù)據(jù)庫環(huán)境

這是一個(gè)能學(xué)到技術(shù)的公眾號，歡迎關(guān)注

重溫SQL Server的行轉(zhuǎn)列和列轉(zhuǎn)行，面試?？碱}

重溫SQL Server的行轉(zhuǎn)列和列轉(zhuǎn)行，面試?？碱}