SqlServer常见问题 （实用代码）

原文出处：http://blog.sina.com.cn/s/blog_4d2d94f20100ldx6.html

有些常见的问题在论坛中不断出现，不妨整理一下。

以下语句是在SQLServer2005上实现的，一些语句无法在SS2000上执行。

有用指数是我根据这个问题的常见程度打的分，仅供参考。实际上，当你遇到了这个问题，这个问题哪怕再少见，解决方案也是非常有用的。

1. 生成若干行记录

有用指数：★★★★★

常见的问题类型：根据起止日期生成若干个日期、生成一天中的各个时间段

《SQL Server 2005技术内幕：T-SQL查询》作者建议在数据库中创建一个数据表：
SQL code
--自然数表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

 

有了这个数字表，可以做很多事情，除上面提到的两个外，还有：生成一批测试数据、生成所有ASCII字符或UNICODE中文字符、等等。

经常有高手使用SELECT number FROM master..spt_values WHERE type = 'P'，这是很妙的方法；但这样只有2048个数字，而且语句太长，不够方便。

总之，一个数字辅助表（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
)
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

这个表可以很容易根据第1条的数字辅助表生成出来。如果经常需要进行日期处理的话，或许会需要这个表。

还可以在这个表中包含一些企业关心的特殊日期，比如开盘日休市日（股票行业）、特殊纪念日和节日、重要员工的生日，等等。这些日期往往是很难计算的，比如中国的法定节假日（农历问题）。

3. 字符串的拼接(Join)与切分(Split)

有用指数：★★★★★

这个问题非常常见！开发中经常需要把一组值以逗号分隔拼接在一个字符串，或是反过来把一个逗号分隔的字符串切分成一组值。
用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 @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)

 

批量的拼接与切分：
SQL code
--测试数据：
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)

 

需要注意的是，倘若分隔符为“;”或者字符串值中包含XML特殊字符（比如&、<、>等等），以上方法可能会无法处理。

4. 树形结构的存储与查询

有用指数：★★★☆☆

数据库设计中常常会遇到需要存储树形结构，比如员工关系表、组织结构表，等等。

SQL code
--测试数据
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

--用递归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地址的存储与查询

有用指数：★★☆☆☆

IPv4的地址实际上是一个4字节的数据。点分十进制的字符串表示是为了人工读写方便，但范围比较则是原始二进制形式方便。因此需要实现二者的相互转换。

SQL code
--测试数据
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

 

6. 中文字符处理

有用指数：★★★★☆

SQLServer中文处理涉及到字符集编码和排序规则，是个非常纠结的问题。参看这篇博客。

SQL code
--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

 

全角半角标点的转换：
SQL code
--full2half
CREATE FUNCTION [dbo].[full2half](
@String nvarchar(max)
)
RETURNS nvarchar(max)
AS

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

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字符串

有用指数：★☆☆☆☆

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

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