SQLServer常见查询问题

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

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

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

1. 生成若干行记录
有用指数：★★★★★

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

《SQL Server 2005技术内幕：T-SQL查询》作者建议在数据库中创建一个数据表：
--自然数表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编程风格》一书建议一个企业的数据库应该创建一个日历表：
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
这个表可以很容易根据第1条的数字辅助表生成出来。如果经常需要进行日期处理的话，或许会需要这个表。

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

3. 字符串的拼接(Join)与切分(Split)
有用指数：★★★★★

这个问题非常常见！开发中经常需要把一组值以逗号分隔拼接在一个字符串，或是反过来把一个逗号分隔的字符串切分成一组值。
用SS2005对XML的支持可以非常方便地实现这个功能。

单变量的拼接与切分：
--将一组查询结果按指定分隔符拼接到一个变量中
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)
批量的拼接与切分：
--测试数据：
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