http://bbs.csdn.net/topics/340078327
1.生成若干行记录
--自然数表1-1M CREATE TABLE Nums(n int NOT NULL PRIMARY KEY CLUSTERED) --书上介绍了很多种填充方法,以下是最高效的一种,需要SS2005的ROW_NUMBER()函数。 WITH B1 AS(SELECT n=1 UNION ALL SELECT n=1), --2 B2 AS(SELECT n=1 FROM B1 a CROSS JOIN B1 b), --4 B3 AS(SELECT n=1 FROM B2 a CROSS JOIN B2 b), --16 B4 AS(SELECT n=1 FROM B3 a CROSS JOIN B3 b), --256 B5 AS(SELECT n=1 FROM B4 a CROSS JOIN B4 b), --65536 CTE AS(SELECT r=ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM B5 a CROSS JOIN B3 b) --65536 * 16 INSERT INTO Nums(n) SELECT TOP(1000000) r FROM CTE ORDER BY r
2.日历表
CREATE TABLE Calendar( date datetime NOT NULL PRIMARY KEY CLUSTERED, weeknum int NOT NULL, weekday int NOT NULL, weekday_desc nchar(3) NOT NULL, is_workday bit NOT NULL, is_weekend bit NOT NULL ) GO WITH CTE1 AS( SELECT date = DATEADD(day,n,'19991231') FROM Nums WHERE n <= DATEDIFF(day,'19991231','20201231')), CTE2 AS( SELECT date, weeknum = DATEPART(week,date), weekday = (DATEPART(weekday,date) + @@DATEFIRST - 1) % 7, weekday_desc = DATENAME(weekday,date) FROM CTE1) --INSERT INTO Calendar SELECT date, weeknum, weekday, weekday_desc, is_workday = CASE WHEN weekday IN (0,6) THEN 0 ELSE 1 END, is_weekend = CASE WHEN weekday IN (0,6) THEN 1 ELSE 0 END FROM CTE2
3.字符串的拼接(Join)与切分(Split)
<strong>--将一组查询结果按指定分隔符拼接到一个变量中 DECLARE @Datebases varchar(max) SET @Datebases = STUFF(( SELECT ','+name FROM sys.databases ORDER BY name FOR XML PATH('')),1,1,'') SELECT @Datebases --将传入的一个参数按指定分隔符切分到一个表中 DECLARE @SourceIDs varchar(max) SET @SourceIDs = 'a,bcd,123,+-*/=,x&y,<key>' SELECT v = x.n.value('.','varchar(10)') FROM ( SELECT ValuesXML = CAST('<root>' + REPLACE((SELECT v = @SourceIDs FOR XML PATH('')),',','</v><v>') + '</root>' AS XML) ) t CROSS APPLY t.ValuesXML.nodes('/root/v') x(n) </strong>
<strong>--测试数据: CREATE TABLE #ToJoin( TableName varchar(20) NOT NULL, ColumnName varchar(20) NOT NULL, PRIMARY KEY CLUSTERED(TableName,ColumnName)) GO CREATE TABLE #ToSplit( TableName varchar(20) NOT NULL PRIMARY KEY CLUSTERED, ColumnNames varchar(max) NOT NULL) GO INSERT INTO #ToJoin VALUES('tblEmployee','EmployeeCode') INSERT INTO #ToJoin VALUES('tblEmployee','EmployeeName') INSERT INTO #ToJoin VALUES('tblEmployee','HireDate') INSERT INTO #ToJoin VALUES('tblEmployee','JobCode') INSERT INTO #ToJoin VALUES('tblEmployee','ReportToCode') INSERT INTO #ToJoin VALUES('tblJob','JobCode') INSERT INTO #ToJoin VALUES('tblJob','JobTitle') INSERT INTO #ToJoin VALUES('tblJob','JobLevel') INSERT INTO #ToJoin VALUES('tblJob','DepartmentCode') INSERT INTO #ToJoin VALUES('tblDepartment','DepartmentCode') INSERT INTO #ToJoin VALUES('tblDepartment','DepartmentName') GO INSERT INTO #ToSplit VALUES('tblDepartment','DepartmentCode,DepartmentName') INSERT INTO #ToSplit VALUES('tblEmployee','EmployeeCode,EmployeeName,HireDate,JobCode,ReportToCode') INSERT INTO #ToSplit VALUES('tblJob','DepartmentCode,JobCode,JobLevel,JobTitle') GO --拼接(Join),SQL Server 2005的FOR XML扩展可以将一个列表转成一个字串: SELECT t.TableName, ColumnNames = STUFF( (SELECT ',' + c.ColumnName FROM #ToJoin c WHERE c.TableName = t.TableName FOR XML PATH('')), 1,1,'') FROM #ToJoin t GROUP BY t.TableName --切分(Split),使用SQL Server 2005对XQuery的支持: SELECT t.TableName, ColumnName = c.ColumnName.value('.','varchar(20)') FROM ( SELECT TableName, ColumnNamesXML = CAST('<Root>' + REPLACE((SELECT ColumnName = ColumnNames FOR XML PATH('')),',','</ColumnName><ColumnName>') + '</Root>' AS xml) FROM #ToSplit ) t CROSS APPLY t.ColumnNamesXML.nodes('/Root/ColumnName') c(ColumnName) </strong>
4.树形结构的存储与查询
--测试数据 CREATE TABLE #Employees( EmployeeCode varchar(20) NOT NULL PRIMARY KEY CLUSTERED, ReportToCode varchar(20) NULL) GO INSERT INTO #Employees VALUES('A',NULL) INSERT INTO #Employees VALUES('B','A') INSERT INTO #Employees VALUES('C','A') INSERT INTO #Employees VALUES('D','A') INSERT INTO #Employees VALUES('E','B') INSERT INTO #Employees VALUES('F','B') INSERT INTO #Employees VALUES('G','C') INSERT INTO #Employees VALUES('H','D') INSERT INTO #Employees VALUES('I','D') INSERT INTO #Employees VALUES('J','D') INSERT INTO #Employees VALUES('K','J') INSERT INTO #Employees VALUES('L','J') INSERT INTO #Employees VALUES('M','J') INSERT INTO #Employees VALUES('N','K') GO /* 可能遇到的查询问题: 1. 员工'D'的所有直接下属 2. 员工'D'的所有2级以内的下属(包括直接下属和直接下属的下属) 3. 员工'N'的所有上级(按报告线顺序列出) 4. 员工@EmployeeCode的所有@LevelDown级以内的下属(@EmployeeCode和@LevelDown以变量传入) DECLARE @EmployeeCode varchar(20), @LevelDown int; SET @EmployeeCode = 'D'; SET @LevelDown = 2; 5. 员工@EmployeeCode的所有@LevelUp级以内的上级(@EmployeeCode和@LevelUp以变量传入) DECLARE @EmployeeCode varchar(20), @LevelUp int; SET @EmployeeCode = 'N'; SET @LevelUp = 2; */ --用递归CTE实现员工树形关系表 WITH CTE AS( SELECT EmployeeCode, ReportToCode, ReportToDepth = 0, ReportToPath = CAST('/' + EmployeeCode + '/' AS varchar(200)) FROM #Employees WHERE ReportToCode IS NULL UNION ALL SELECT e.EmployeeCode, e.ReportToCode, ReportToDepth = mgr.ReportToDepth + 1, ReportToPath = CAST(mgr.ReportToPath + e.EmployeeCode + '/' AS varchar(200)) FROM #Employees e INNER JOIN CTE mgr ON e.ReportToCode = mgr.EmployeeCode ) SELECT * FROM CTE ORDER BY ReportToPath
5.IPv4地址的存储与查询
<strong>--测试数据 CREATE TABLE #IPs( strIP varchar(15) NULL, binIP binary(4) NULL) GO INSERT INTO #IPs VALUES('0.0.0.0',NULL) INSERT INTO #IPs VALUES('255.255.255.255',NULL) INSERT INTO #IPs VALUES('127.0.0.1',NULL) INSERT INTO #IPs VALUES('192.168.43.192',NULL) INSERT INTO #IPs VALUES('192.168.1.101',NULL) INSERT INTO #IPs VALUES('65.54.239.80',NULL) INSERT INTO #IPs VALUES(NULL,0xB92AEAD3) INSERT INTO #IPs VALUES(NULL,0x2D4B2E53) INSERT INTO #IPs VALUES(NULL,0x31031B0B) INSERT INTO #IPs VALUES(NULL,0x7C2D5F2F) INSERT INTO #IPs VALUES(NULL,0x473E5D31) INSERT INTO #IPs VALUES(NULL,0x90D7D66B) GO SELECT strIP,binIP, strIP_new = CAST(CAST(SUBSTRING(binIP,1,1) AS int) AS varchar(3)) + '.' + CAST(CAST(SUBSTRING(binIP,2,1) AS int) AS varchar(3)) + '.' + CAST(CAST(SUBSTRING(binIP,3,1) AS int) AS varchar(3)) + '.' + CAST(CAST(SUBSTRING(binIP,4,1) AS int) AS varchar(3)), binIP_new = CAST(CAST(PARSENAME(strIP,4) AS int) AS binary(1)) + CAST(CAST(PARSENAME(strIP,3) AS int) AS binary(1)) + CAST(CAST(PARSENAME(strIP,2) AS int) AS binary(1)) + CAST(CAST(PARSENAME(strIP,1) AS int) AS binary(1)), intIP_new = CAST(PARSENAME(strIP,1) AS bigint) + CAST(PARSENAME(strIP,2) AS bigint) * 256 + CAST(PARSENAME(strIP,3) AS bigint) * 65536 + CAST(PARSENAME(strIP,4) AS bigint) * 16777216 --int类型也可以,但浪费空间且不直观 FROM #IPs </strong>
6.中文字符处理
--ASCII字符 SELECT n,x=CAST(n AS binary(2)),u=NCHAR(n) FROM Nums WHERE n BETWEEN 32 AND 126 --UNICODE中文字符 SELECT n,x=CAST(n AS binary(2)),u=NCHAR(n) FROM Nums WHERE n BETWEEN 19968 AND 40869 19968 0x4E00 一 40869 0x9FA5 龥 --以下两个条件用来判断字符串是否包含汉字 LIKE N'%[吖-咗]%' COLLATE Chinese_PRC_CI_AS LIKE N'%[一-龥]%' COLLATE Chinese_PRC_BIN --这是因为在以上两种不同的排序规则下,汉字的排列顺序是不同的。 --中文全角标点符号 SELECT n,x=CAST(n AS binary(2)),uq=NCHAR(n),ub=NCHAR(n-65248) FROM Nums WHERE n BETWEEN 65281 AND 65374 SELECT NCHAR(12288),NCHAR(32) 65281 0xFF01 ! ! 65374 0xFF5E ~ ~ --以下条件用来判断字符串是否包含全角标点 LIKE N'%[!-~]%' COLLATE Chinese_PRC_BIN
--full2half CREATE FUNCTION [dbo].[full2half]( @String nvarchar(max) ) RETURNS nvarchar(max) AS /* 全角(Fullwidth)转换为半角(Halfwidth) */ BEGIN DECLARE @chr nchar(1) DECLARE @i int SET @String = REPLACE(@String,N' ',N' ') SET @i = PATINDEX(N'%[!-~]%' COLLATE Latin1_General_BIN,@String) WHILE @i > 0 BEGIN SET @chr = SUBSTRING(@String,@i,1) SET @String = REPLACE(@String,@chr,NCHAR(UNICODE(@chr)-65248)) SET @i = PATINDEX(N'%[!-~]%' COLLATE Latin1_General_BIN,@String) END RETURN @String END GO CREATE FUNCTION [dbo].[half2full]( @String nvarchar(max) ) RETURNS nvarchar(max) AS /* 半角(Halfwidth)转换为全角(Fullwidth) */ BEGIN DECLARE @chr nchar(1) DECLARE @i int SET @String = REPLACE(@String,N' ',N' ') SET @i = PATINDEX(N'%[!-~]%' COLLATE Latin1_General_BIN,@String) WHILE @i > 0 BEGIN SET @chr = SUBSTRING(@String,@i,1) SET @String = REPLACE(@String,@chr,NCHAR(UNICODE(@chr)+65248)) SET @i = PATINDEX(N'%[!-~]%' COLLATE Latin1_General_BIN,@String) END RETURN @String END GO
7.binary字符串
--string到binary可以用这个系统函数sys.fn_varbintohexstr()(实际上是master.dbo.fn_varbintohexstr) SELECT sys.fn_varbintohexstr(0x1234),'0x1234' --binary到string需要自定义函数 CREATE FUNCTION dbo.hexstr2varbin( @hexstr varchar(max) ) RETURNS varbinary(max) AS /* 将表示16进制的字符串转换为2进制类型 --TESTCASES SELECT dbo.hexstr2varbin(NULL),NULL SELECT dbo.hexstr2varbin(''),0x SELECT dbo.hexstr2varbin('0x'),0x SELECT dbo.hexstr2varbin('30394161'),0x30394161 SELECT dbo.hexstr2varbin('0x30394161'),0x30394161 SELECT dbo.hexstr2varbin('0x1A2B3C4D5E6F'),0x1A2B3C4D5E6F SELECT dbo.hexstr2varbin('0x1a2b3c4d5e6f'),0x1a2b3c4d5e6f --UNIMPLEMENTED SELECT dbo.hexstr2varbin('0x3039416'),0x3039416 */ BEGIN DECLARE @value int DECLARE @ascii int DECLARE @varbin varbinary(max) IF @hexstr LIKE '0x%' SET @hexstr = STUFF(@hexstr,1,2,'') SET @hexstr = UPPER(@hexstr) IF @hexstr NOT LIKE '%[^0-9A-F]%' COLLATE Chinese_PRC_BIN BEGIN SET @varbin = 0x WHILE @hexstr <> '' BEGIN SET @value = ASCII(SUBSTRING(@hexstr,1,1)) IF @value <= 57 SET @value = @value - 48 ELSE SET @value = @value - 55 SET @ascii = @value * 16 SET @value = ASCII(SUBSTRING(@hexstr,2,1)) IF @value <= 57 SET @value = @value - 48 ELSE SET @value = @value - 55 SET @ascii = @ascii + @value SET @varbin = @varbin + CAST(@ascii AS binary(1)) SET @hexstr = STUFF(@hexstr,1,2,'') END END RETURN @varbin END GO