SQLServer常见查询有关问题-MSSQL教程-爱易网页

SQLServer常见查询有关问题

日期：2014-05-18　浏览次数：20698 次

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

以下语句是在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(  4

这个表可以很容易根据第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 I

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

SQLServer常见查询有关问题

相关资料更多>

推荐阅读更多>