重温 SQL 的行转列和列转行，面试常考题「终于解决」
Hi，大家好，我是编程小6，很荣幸遇见你，我把这些年在开发过程中遇到的问题或想法写出来，今天说一说重温 SQL 的行转列和列转行，面试常考题「终于解决」,希望能够帮助你!!!。
来源：
SQL数据库开发
获取【SQL必备50题】
CREATE TABLE [StudentScores]([UserName] NVARCHAR(20), --学生姓名[Subject] NVARCHAR(30), --科目[Score] FLOAT, --成绩)INSERT INTO [StudentScores] SELECT 'Nick', '语文', 80INSERT INTO [StudentScores] SELECT 'Nick', '数学', 90INSERT INTO [StudentScores] SELECT 'Nick', '英语', 70INSERT INTO [StudentScores] SELECT 'Nick', '生物', 85INSERT INTO [StudentScores] SELECT 'Kent', '语文', 80INSERT INTO [StudentScores] SELECT 'Kent', '数学', 90INSERT INTO [StudentScores] SELECT 'Kent', '英语', 70INSERT INTO [StudentScores] SELECT 'Kent', '生物', 85
SELECTUserName,MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',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, --充值时间 [PayType] NVARCHAR(20), --充值类型 [Money] DECIMAL, --充值金额 [IsSuccess] BIT, --是否成功 1表示成功， 0表示失败 CONSTRAINT [PK_Inpours_ID] PRIMARY KEY(ID))INSERT INTO Inpours SELECT '张三', '2010-05-01', '支付宝', 50, 1INSERT INTO Inpours SELECT '张三', '2010-06-14', '支付宝', 50, 1INSERT INTO Inpours SELECT '张三', '2010-06-14', '手机短信', 100, 1INSERT INTO Inpours SELECT '李四', '2010-06-14', '手机短信', 100, 1INSERT INTO Inpours SELECT '李四', '2010-07-14', '支付宝', 100, 1INSERT INTO Inpours SELECT '王五', '2010-07-14', '工商银行卡', 100, 1INSERT INTO Inpours SELECT '赵六', '2010-07-14', '建设银行卡', 100,1
SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime, CASE PayType WHEN '支付宝' THEN SUM(Money) ELSE 0 END AS '支付宝', CASE PayType WHEN '手机短信' THEN SUM(Money) ELSE 0 END AS '手机短信', CASE PayType WHEN '工商银行卡' THEN SUM(Money) ELSE 0 END AS '工商银行卡', CASE PayType WHEN '建设银行卡' THEN SUM(Money) ELSE 0 END AS '建设银行卡'FROM InpoursGROUP BY CreateTime, PayType
SELECTCreateTime,IS(SUM([支付宝]) , 0) AS [支付宝],IS(SUM([手机短信]) , 0) AS [手机短信],IS(SUM([工商银行卡]), 0) AS [工商银行卡],IS(SUM([建设银行卡]), 0) AS [建设银行卡]FROM(SELECTCONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,CASE PayType WHEN '支付宝' THEN SUM(Money) ELSE 0 END AS '支付宝' ,CASE PayType WHEN '手机短信' THEN SUM(Money) ELSE 0 END AS '手机短信',CASE PayType WHEN '工商银行卡' THEN SUM(Money) ELSE 0 END AS '工商银行卡',CASE PayType WHEN '建设银行卡' THEN SUM(Money) ELSE 0 END AS '建设银行卡'FROM InpoursGROUP BY CreateTime, PayType) TGROUP BY CreateTime
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 + ' IS(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 @cmdTextEXECUTE (@cmdText);
扫码回复【SQL50】
获取【SQL必备50题】
SELECT CreateTime, [支付宝] , [手机短信],[工商银行卡] , [建设银行卡]FROM( SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType, Money FROM Inpours) PPIVOT ( SUM(Money) FOR PayType IN ([支付宝], [手机短信], [工商银行卡], [建设银行卡]) ) AS TORDER BY CreateTime
消息 325，级别 15，状态 1，第 9 行
‘PIVOT’ 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。
Create Table ProgrectDetail(ProgrectName NVARCHAR(20), --工程名称OverseaSupply INT, --海外供应商供给数量NativeSupply INT, --国内供应商供给数量SouthSupply INT, --南方供应商供给数量NorthSupply INT --北方供应商供给数量)INSERT INTO ProgrectDetailSELECT 'A', 100, 200, 50, 50UNION ALLSELECT 'B', 200, 300, 150, 150UNION ALLSELECT 'C', 159, 400, 20, 320UNION ALLSELECT 'D', 250, 30, 15, 15
SELECT ProgrectName, 'OverseaSupply' AS Supplier, MAX(OverseaSupply) AS 'SupplyNum'FROM ProgrectDetailGROUP BY ProgrectNameUNION ALLSELECT ProgrectName, 'NativeSupply' AS Supplier, MAX(NativeSupply) AS 'SupplyNum'FROM ProgrectDetailGROUP BY ProgrectNameUNION ALLSELECT ProgrectName, 'SouthSupply' AS Supplier, MAX(SouthSupply) AS 'SupplyNum'FROM ProgrectDetailGROUP BY ProgrectNameUNION ALLSELECT ProgrectName, 'NorthSupply' AS Supplier, MAX(NorthSupply) AS 'SupplyNum'FROM ProgrectDetailGROUP BY ProgrectName
SELECT ProgrectName,Supplier,SupplyNumFROM ( SELECT ProgrectName, OverseaSupply, NativeSupply, SouthSupply, NorthSupply FROM ProgrectDetail)TUNPIVOT ( SupplyNum FOR Supplier IN (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )) P
获取【SQL必备50题】
上一篇
已是最后文章
下一篇
已是最新文章
最新文章
