日期:2014-05-18 浏览次数:20755 次
--自然数表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
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
--将一组查询结果按指定分隔符拼接到一个变量中 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 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