SQLSERVER2008 （T-SQL汇总一）

来源：互联网发布：Java web 项目开发文档编辑：程序博客网时间：2024/05/04 13:14
1、全局变量： T-SQL 汇总之一 全局变量复制代码select APP_NAME ( ) as w --当前会话的应用程序 select @@ERROR    --返回最后执行的 Transact-SQL 语句的错误代码(integer)（如果最后一条语句不出错，就一直是0） select @@IDENTITY   --返回最后插入的标识值 select USER_NAME()    --返回用户数据库用户名 select @@CONNECTIONS  --返回自上次SQL启动以来连接或试图连接的次数。 select GETDATE() --当前时间 select @@CPU_BUSY/100  --返回自上次启动SQL 以来 CPU 的工作时间，单位为毫秒 USE tempdb select @@DBTS  as w  --为当前数据库返回当前 timestamp 数据类型的值。这一 timestamp 值保证在数据库中是唯一的。 select @@IDLE  as w  --返回SQL自上次启动后闲置的时间，单位为毫秒 select @@IO_BUSY AS w   --返回SQL自上次启动后用于执行输入和输出操作的时间，单位为毫秒 select @@LANGID AS w   --返回当前所使用语言的本地语言标识符(ID) select @@LANGUAGE AS w   --返回当前使用的语言名 select @@LOCK_TIMEOUT as w  --当前会话的当前锁超时设置，单位为毫秒。 select @@MAX_CONNECTIONS  as w  --返回SQL上允许的同时用户连接的最大数。返回的数不必为当前配置的数值 EXEC sp_configure  --显示当前服务器的全局配置设置 select @@MAX_PRECISION as w --返回 decimal 和 numeric 数据类型所用的精度级别，即该服务器中当前设置的精度。默认最大精度38。 select @@OPTIONS  as w  --返回当前 SET 选项的信息。 select @@PACK_RECEIVED as w  --返回SQL自启动后从网络上读取的输入数据包数目。 select @@PACK_SENT as w  --返回SQ自上次启动后写到网络上的输出数据包数目。 select @@PACKET_ERRORS as w  --返回自SQL启动后，在SQL连接上发生的网络数据包错误数。 select @@SERVERNAME as w --返回运行SQL服务器名称。 select @@SERVICENAME  as w --返回SQL正在其下运行的注册表键名 select @@TIMETICKS  as w --返回SQL服务器一刻度的微秒数 select @@TOTAL_ERRORS AS w  --返回 SQL服务器自启动后，所遇到的磁盘读/写错误数。 select @@TOTAL_READ  as w  --返回 SQL服务器自启动后读取磁盘的次数。 select @@TOTAL_WRITE as w  --返回SQL服务器自启动后写入磁盘的次数。 select @@TRANCOUNT  as w  --返回当前连接的活动事务数。 select @@VERSION as w  --返回SQL服务器安装的日期、版本和处理器类型。复制代码T-SQL 汇总之二 备份语句复制代码 --查询文件是否存在 CREATE TABLE #tmp ([File Exists] BIT, [File is a Directory] BIT, [Parent Directory Exists] BIT)  INSERT INTO #tmp ([File Exists], [File is a Directory], [Parent Directory Exists])  EXEC master.dbo.xp_fileexist 'D:\Test.bak'  SELECT * FROM #tmp  DROP TABLE #tmp --完整备份数据库 BACKUP DATABASE MyBakTest TO DISK='D:\Test.bak' WITH FORMAT --对tb表插入第一条记录 INSERT INTO tb_user(uname) VALUES('第一1次差异备份') --第一次差异备份 BACKUP DATABASE MyBakTest TO DISK='D:\Test_One_Dif.bak' WITH FORMAT,DIFFERENTIAL --对tb表插入第二条记录 INSERT INTO tb_user(uname) VALUES('第二3次差异备份') --第二次差异备份 BACKUP DATABASE MyBakTest TO DISK='D:\Test_Two_Dif.bak' WITH FORMAT,DIFFERENTIAL --OK.现在直接还原.完整备份+第二次差异备份.看看第二条记录在不在. GO --完整备份还原 RESTORE DATABASE MyBackTest2 FROM DISK='D:\Test.bak' WITH REPLACE,NORECOVERY RESTORE DATABASE MyBackTest2 FROM DISK='D:\Test_one_Dif.bak' WITH RECOVERY --第二次差异备份还原 RESTORE DATABASE MyBackTest2 FROM DISK='D:\Test_Two_Dif.bak' WITH RECOVERY复制代码T-SQL 汇总之三 行转列复制代码 --行转列 create table #CarLog(LogDate datetime,PathLine nvarchar(10),Amount int) --drop table #CarLog insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','1号线',1) insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-25','1号线',91) insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-26','1号线',66) insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','3号线',44) insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','3号线',33) insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','5号线',12) insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','6号线',22) select * from #CarLog --方法1 SELECT LogDate, (CASE WHEN PathLine='1号线' THEN Amount ELSE 0 END)AS '1号线', (CASE WHEN PathLine='2号线' THEN Amount ELSE 0 END)AS '2号线', (CASE WHEN PathLine='3号线' THEN Amount ELSE 0 END)AS '3号线', (CASE WHEN PathLine='4号线' THEN Amount ELSE 0 END)AS '4号线', (CASE WHEN PathLine='5号线' THEN Amount ELSE 0 END)AS '5号线', (CASE WHEN PathLine='6号线' THEN Amount ELSE 0 END)AS '6号线' FROM #CarLog  Group By PathLine,LogDate,Amount --方法2 declare @Str nvarchar(max) set @str='select [LogDate]' select @str=@str+',['+PathLine+']' from #CarLog group by PathLine --exec @str print @str set @str=@str+' FROM (  SELECT LogDate,Amount,PathLine  FROM #CarLog ) AS c PIVOT ( sum(Amount) FOR PathLine IN   (' select @str=@str+'['+PathLine+'],' from #CarLog group by PathLine set @str=left(@str,Len(@str)-1) set @str=@str+ ')) AS thePivot ORDER BY LogDate' print @str declare @Table table(Logdate datetime,1号线 int, 3号线 int,4号线 int,5号线 int,6号线 int) INSERT INTO @Table exec(@str) SELECT * FROM @Table --drop table #T select [LogDate],[1号线],[3号线],[5号线],[6号线] FROM (  SELECT LogDate,Amount,PathLine  FROM #CarLog ) AS cl PIVOT ( sum(Amount) FOR PathLine IN   ([1号线],[3号线],[5号线],[6号线])) AS thePivot ORDER BY LogDate ----------------------------------分割线----------------------------------------------------- CREATE  TABLE [#StudentScores] (     [UserName]         NVARCHAR(20),        --学生姓名     [Subject]          NVARCHAR(30),        --科目     [Score]            FLOAT,               --成绩 ) INSERT INTO [#StudentScores] SELECT 'Nick', '语文', 81 INSERT INTO [#StudentScores] SELECT 'Nick', '数学', 91 INSERT INTO [#StudentScores] SELECT 'Nick', '英语', 72 INSERT INTO [#StudentScores] SELECT 'Nick', '生物', 83 INSERT INTO [#StudentScores] SELECT 'Kent', '语文', 84 INSERT INTO [#StudentScores] SELECT 'Kent', '数学', 99 INSERT INTO [#StudentScores] SELECT 'Kent', '英语', 77 INSERT INTO [#StudentScores] SELECT 'Kent', '生物', 79 --drop table #StudentScores --------------------------- select * from #StudentScores SELECT        UserName,        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, 1 INSERT INTO [#Inpours] SELECT '张三', '2010-06-14', '支付宝', 50, 1 INSERT INTO [#Inpours] SELECT '张三', '2010-06-14', '手机短信', 100, 1 INSERT INTO [#Inpours] SELECT '李四', '2010-06-14', '手机短信', 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', '建设银行卡', 100, 1 select * from #Inpours 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 #Inpours GROUP BY CreateTime, PayType --------------- SELECT         CreateTime,         ISNULL(SUM([支付宝]), 0) AS [支付宝],         ISNULL(SUM([手机短信]), 0) AS [手机短信],        ISNULL(SUM([工商银行卡]), 0) AS [工商银行卡],         ISNULL(SUM([建设银行卡]), 0) AS [建设银行卡] FROM (     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 #Inpours     GROUP BY CreateTime, PayType ) T GROUP BY CreateTime ---------------------- DECLARE @cmdText    VARCHAR(8000); DECLARE @tmpSql        VARCHAR(8000); SET @cmdText = 'SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,' + CHAR(10); print @cmdText SELECT @cmdText = @cmdText + ' CASE PayType WHEN ''' + PayType + ''' THEN SUM(Money) ELSE 0 END AS ''' + PayType                  + ''',' + CHAR(10)  FROM (SELECT DISTINCT PayType FROM #Inpours ) T print @cmdText SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意这里，如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1) SET @cmdText = @cmdText + ' FROM #Inpours     GROUP BY CreateTime, PayType '; print @cmdText SET @tmpSql ='SELECT CreateTime,' + CHAR(10); SELECT @tmpSql = @tmpSql + ' ISNULL(SUM(' + PayType  + '), 0) AS ''' + PayType  + ''','  + CHAR(10)                     FROM  (SELECT DISTINCT PayType FROM #Inpours ) T print @tmpSql SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ' FROM (' + CHAR(10); print @tmpSql SET @cmdText = @tmpSql + @cmdText + ') T GROUP BY CreateTime '; PRINT @cmdText EXECUTE (@cmdText); ------------------------ SELECT          CreateTime, [支付宝] , [手机短信],[工商银行卡] , [建设银行卡] FROM (     SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType, Money     FROM #Inpours ) P PIVOT (             SUM(Money)             FOR PayType IN             ([支付宝], [手机短信], [工商银行卡], [建设银行卡])       ) AS T ORDER BY CreateTime ------------------- /* 消息 325，级别 15，状态 1，第 9 行 'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。 这个是因为：对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。 有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。  例如，只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在数据库的名称。 **/ ------------------------------------ CREATE TABLE #ProgrectDetail (     ProgrectName         NVARCHAR(20), --工程名称     OverseaSupply        INT,          --海外供应商供给数量     NativeSupply         INT,          --国内供应商供给数量     SouthSupply          INT,          --南方供应商供给数量     NorthSupply          INT           --北方供应商供给数量 ) --drop table #ProgrectDetail 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 select * from #ProgrectDetail ----------------- 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 -------------------- SELECT ProgrectName,Supplier,SupplyNum FROM  (     SELECT ProgrectName, OverseaSupply, NativeSupply,            SouthSupply, NorthSupply      FROM #ProgrectDetail )T UNPIVOT  (     SupplyNum FOR Supplier IN     (OverseaSupply, NativeSupply, SouthSupply, NorthSupply ) ) P复制代码
阅读全文
0 0