SQLServer常见查询问题
有些常见的问题在论坛中不断出现，不妨整理一下。
以下语句是在SQLServer2005上实现的，一些语句无法在SS2000上执行。
有用指数是我根据这个问题的常见程度打的分，仅供参考。实际上，当你遇到了这个问题，这个问题哪怕再少见，解决方案也是非常有用的。
1. 生成若干行记录
有用指数：★★★★★
常见的问题类型：根据起止日期生成若干个日期、生成一天中的各个时间段
《SQL Server 2005技术内幕：T-SQL查询》作者建议在数据库中创建一个数据表：
--自然数表1-1MCREATE TABLE Nums(n int NOT NULL PRIMARY KEY CLUSTERED)--书上介绍了很多种填充方法，以下是最高效的一种，需要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 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
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( 4
--将一组查询结果按指定分隔符拼接到一个变量中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)) tCROSS APPLY t.ValuesXML.nodes('/root/v') x(n)
--测试数据：
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)
--测试数据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('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--测试数据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)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)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) 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
--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 4086919968 0x4E00 一40869 0x9FA5 龥--以下两个条件用来判断字符串是否包含汉字LIKE N'%[吖-咗]%' COLLATE Chinese_PRC_CI_ASLIKE N'%[一-龥]%' COLLATE Chinese_PRC_BIN--这是因为在以上两种不同的排序规则下，汉字的排列顺序是不同的。--中文全角标点符号SELECT n,x=CAST(n AS binary(2)),uq=NCHAR(n),ub=NCHAR(n-65248) FROMas 规格3
--full2halfCREATE 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 @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_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 @StringENDGO