重溫 SQL 的行轉(zhuǎn)列和列轉(zhuǎn)行，面試?？碱}
點(diǎn)擊關(guān)注上方“SQL數(shù)據(jù)庫開發(fā)”，
設(shè)為“置頂或星標(biāo)”，第一時(shí)間送達(dá)干貨
SQL專欄
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
(提示：可以左右滑動(dòng)代碼)
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
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, 1SELECT
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
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 CreateTimeDECLARE @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);
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í)可能會(huì)出現(xiàn)這樣的錯(cuò)誤：
消息 325，級(jí)別 15，狀態(tài) 1，第 9 行
‘PIVOT’ 附近有語法錯(cuò)誤。您可能需要將當(dāng)前數(shù)據(jù)庫的兼容級(jí)別設(shè)置為更高的值，以啟用此功能。有關(guān)存儲(chǔ)過程 sp_dbcmptlevel 的信息，請參見幫助。
這個(gè)是因?yàn)椋簩ι?jí)到 SQL Server 2005 或更高版本的數(shù)據(jù)庫使用 PIVOT 和 UNPIVOT 時(shí)，必須將數(shù)據(jù)庫的兼容級(jí)別設(shè)置為 90 或更高。有關(guān)如何設(shè)置數(shù)據(jù)庫兼容級(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, 15SELECT 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推薦閱讀
50 款數(shù)據(jù)可視化分析工具大集合，總有一款適合你 悟空無姓無名的時(shí)候，閻王生死簿是怎么寫的呢？ 5號(hào)發(fā)工資和25號(hào)發(fā)工資，還能看出公司是否靠譜？?? 如果把14億中國人都拉到一個(gè)微信群。。。 假如面試中大家都說實(shí)話....
后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群
