SQLServer常见查询问题.docx

1、SQLServer常见查询问题有些常见的问题在论坛中不断出现，不妨整理一下。以下语句是在SQLServer2005上实现的，一些语句无法在SS2000上执行。有用指数是我根据这个问题的常见程度打的分，仅供参考。实际上，当你遇到了这个问题，这个问题哪怕再少见，解决方案也是非常有用的。1. 生成若干行记录有用指数：常见的问题类型：根据起止日期生成若干个日期、生成一天中的各个时间段SQL Server 2005技术内幕：T-SQL查询作者建议在数据库中创建一个数据表：SQL code -自然数表1-1MCREATE TABLE Nums(n int NOT NULL PRIMARY KEY CLUS

2、TERED)-书上介绍了很多种填充方法，以下是最高效的一种，需要SS2005的ROW_NUMBER()函数。WITH B1 AS(SELECT n=1 UNION ALL SELECT n=1), -2B2 AS(SELECT n=1 FROM B1 a CROSS JOIN B1 b), -4B3 AS(SELECT n=1 FROM B2 a CROSS JOIN B2 b), -16B4 AS(SELECT n=1 FROM B3 a CROSS JOIN B3 b), -256B5 AS(SELECT n=1 FROM B4 a CROSS JOIN B4 b), -65536CTE

3、AS(SELECT r=ROW_NUMBER() OVER(ORDER BY (SELECT 1) FROM B5 a CROSS JOIN B3 b) -65536 * 16INSERT INTO Nums(n)SELECT TOP(1000000) r FROM CTE ORDER BY r有了这个数字表，可以做很多事情，除上面提到的两个外，还有：生成一批测试数据、生成所有ASCII字符或UNICODE中文字符、等等。经常有高手使用SELECT number FROM master.spt_values WHERE type = P，这是很妙的方法；但这样只有2048个数字，而且语句太长，

4、不够方便。总之，一个数字辅助表（10万还是100万根据个人需要而定），你值得拥有。2. 日历表有用指数：SQL编程风格一书建议一个企业的数据库应该创建一个日历表：SQL code 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)GOWITH CTE1 AS(

5、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 CalendarSELECT date, weeknum, weekday, w

6、eekday_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 ENDFROM CTE2这个表可以很容易根据第1条的数字辅助表生成出来。如果经常需要进行日期处理的话，或许会需要这个表。还可以在这个表中包含一些企业关心的特殊日期，比如开盘日休市日（股票行业）、特殊纪念日和节日、重要员工的生日，等等。这些日期往往是很难计算的，比如中国的法定节假日（农历问题）。3. 字符串的拼接(Join)与切分(Split)有用

7、指数：这个问题非常常见！开发中经常需要把一组值以逗号分隔拼接在一个字符串，或是反过来把一个逗号分隔的字符串切分成一组值。用SS2005对XML的支持可以非常方便地实现这个功能。单变量的拼接与切分：SQL code -将一组查询结果按指定分隔符拼接到一个变量中DECLARE Datebases varchar(max)SET Datebases = STUFF( SELECT ,+name FROM sys.databases ORDER BY name FOR XML PATH(),1,1,)SELECT Datebases-将传入的一个参数按指定分隔符切分到一个表中DECLARE Sourc

8、eIDs varchar(max)SET SourceIDs = a,bcd,123,+-*/=,x&y,SELECT v = x.n.value(.,varchar(10)FROM ( SELECT ValuesXML = CAST( + REPLACE(SELECT v = SourceIDs FOR XML PATH(),) + AS XML) tCROSS APPLY t.ValuesXML.nodes(/root/v) x(n)批量的拼接与切分：SQL code -测试数据：CREATE TABLE #ToJoin( TableName varchar(20) NOT NULL, C

9、olumnName varchar(20) NOT NULL, PRIMARY KEY CLUSTERED(TableName,ColumnName)GOCREATE TABLE #ToSplit( TableName varchar(20) NOT NULL PRIMARY KEY CLUSTERED, ColumnNames varchar(max) NOT NULL)GOINSERT INTO #ToJoin VALUES(tblEmployee,EmployeeCode)INSERT INTO #ToJoin VALUES(tblEmployee,EmployeeName)INSERT

10、 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(tblJ

11、ob,DepartmentCode)INSERT INTO #ToJoin VALUES(tblDepartment,DepartmentCode)INSERT INTO #ToJoin VALUES(tblDepartment,DepartmentName)GOINSERT INTO #ToSplit VALUES(tblDepartment,DepartmentCode,DepartmentName)INSERT INTO #ToSplit VALUES(tblEmployee,EmployeeCode,EmployeeName,HireDate,JobCode,ReportToCode)

12、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 tGROUP BY t.TableName-切分(S

13、plit)，使用SQL Server 2005对XQuery的支持：SELECT t.TableName, ColumnName = c.ColumnName.value(.,varchar(20)FROM ( SELECT TableName, ColumnNamesXML = CAST( + REPLACE(SELECT ColumnName = ColumnNames FOR XML PATH(),) + AS xml) FROM #ToSplit) tCROSS APPLY t.ColumnNamesXML.nodes(/Root/ColumnName) c(ColumnName)需要

14、注意的是，倘若分隔符为“;”或者字符串值中包含XML特殊字符（比如&、等等），以上方法可能会无法处理。4. 树形结构的存储与查询有用指数：数据库设计中常常会遇到需要存储树形结构，比如员工关系表、组织结构表，等等。SQL code -测试数据CREATE TABLE #Employees( EmployeeCode varchar(20) NOT NULL PRIMARY KEY CLUSTERED, ReportToCode varchar(20) NULL)GOINSERT INTO #Employees VALUES(A,NULL)INSERT INTO #Employees VALUES

15、(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

16、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

17、 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(/ + Empl

18、oyeeCode + / 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.Emplo

19、yeeCode)SELECT * FROM CTE ORDER BY ReportToPath5. IPv4地址的存储与查询有用指数：IPv4的地址实际上是一个4字节的数据。点分十进制的字符串表示是为了人工读写方便，但范围比较则是原始二进制形式方便。因此需要实现二者的相互转换。SQL code -测试数据CREATE TABLE #IPs( strIP varchar(15) NULL, binIP binary(4) NULL)GOINSERT INTO #IPs VALUES(0.0.0.0,NULL)INSERT INTO #IPs VALUES(255.255.255.255,NULL

20、)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

21、 #IPs VALUES(NULL,0x7C2D5F2F)INSERT INTO #IPs VALUES(NULL,0x473E5D31)INSERT INTO #IPs VALUES(NULL,0x90D7D66B)GOSELECT 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)

22、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_n

23、ew = 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 #IPs6. 中文字符处理有用指数：SQLServer中文处理涉及到字符集编码和排序规则，是个非常纠结的问题。参看这篇博客。SQL code -ASCII字符SELECT n,x=CAST(n AS

24、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 4086919968 0x4E00 一40869 0x9FA5 龥-以下两个条件用来判断字符串是否包含汉字LIKE N%吖-咗% COLLATE Chinese_PRC_CI_ASLIKE N%一-龥% COLLATE Chinese_PRC_BIN-这是因为在以上两种不同的排序规则下，汉字的排列顺序是不

25、同的。-中文全角标点符号SELECT n,x=CAST(n AS binary(2),uq=NCHAR(n),ub=NCHAR(n-65248) FROM Nums WHERE n BETWEEN 65281 AND 65374SELECT NCHAR(12288),NCHAR(32)65281 0xFF01 ！ !65374 0xFF5E -以下条件用来判断字符串是否包含全角标点LIKE N%！-% COLLATE Chinese_PRC_BIN全角半角标点的转换：SQL code -full2halfCREATE FUNCTION dbo.full2half(String nvarchar

26、(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)

27、SET i = PATINDEX(N%！-% COLLATE Latin1_General_BIN,String) END RETURN StringENDGOCREATE 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_G

28、eneral_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 StringENDGO暂时就这些。欢迎拍砖。 对我有用3 丢个板砖0 引用 举报 管理 TOP 回复次数：289 feilniu (feilniu) 等级： #1楼 得分：0回复于：2010-05-28 16:2

29、3:277. binary字符串有用指数：0x1234与0x1234的相互转换。很明显，CAST/CONVERT是不行的。SQL code -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进制类型-TESTCASESSELECT dbo.hexstr2varbi