</pre><pre name="code" class="sql">--生成一个"序列" 或者说生成一个"虚拟数字辅助表"是扩展数据库集合操作的重要途径。其主要创建途径一般是通过笛卡尔积法、系统表法(实质一般也是笛卡尔积法)、创建自定义函数法等方式。  

--笛卡尔积法，先构建一个包含10行记录的辅助表，选择10行记录主要是为了方便交叉连接时按照10进制扩展行数

WITH t1 AS (

SELECT 1 AS RN

UNION All

SELECT 1

UNION All

SELECT 1

UNION All

SELECT 1

UNION All

SELECT 1

UNION All

SELECT 1

UNION All

SELECT 1

UNION All

SELECT 1

UNION All

SELECT 1

UNION All

SELECT 1

)

SELECT NUM=ROW_NUMBER() OVER(ORDER BY a.RN) FROM T1 a,T1 b,T1 c,T1 d,T1 e,T1 f,T1 g

--生成e10+8(一千万)耗时01:12  

--递归法，递归法另一用途是层次查询遍历各级节点；在实现各种复杂数学序列中亦有应用

--注意当递归次数过百时需加OPTION(MAXRECURSION 0)控制条件使数据库不限定递归次数

WITH T1 AS (

SELECT 1 AS NUM

UNION ALL

SELECT T1.NUM+1

FROM T1

WHERE T1.NUM<10000000)

SELECT NUM FROM T1

OPTION(MAXRECURSION 0)

--生成e10+8(一千万)耗时02:45  

--系统表法，生成0-2047

SELECT number FROM master..spt_values WHERE type='p'  

SELECT TOP 10000000 NUM=ROW_NUMBER() OVER(ORDER BY GETDATE())

FROM syscolumns a,syscolumns b,syscolumns c

--生成e10+8(一千万)耗时01:16  

--自定义函数法

-- definition of GetNums function, SQL Server 2012 version

--注意生成的"虚拟数字辅助表"的列别名是"n"

USE TSQL2012;

IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;

GO

CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE

AS

RETURN

  WITH

    L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),

    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum

            FROM L5)

  SELECT @low + rownum - 1 AS n

  FROM Nums

  ORDER BY rownum

  OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;

GO  

-- definition of GetNums function, pre-SQL Server 2012 version

IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL

  DROP FUNCTION dbo.GetNums;

GO

CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE

AS

RETURN

  WITH

    L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),

    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum

            FROM L5)

  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n

  FROM Nums

  ORDER BY rownum;

GO  

-- test function

--生成e10+8(一千万)耗时01:18

SELECT n FROM dbo.GetNums(1, 10000000);  

--生成11至20的序列

SELECT n FROM dbo.GetNums(11, 20);  

--用函数生成日期序列，注意日期序列可由递归法直接生成，但日期类型必须是datetime，不能是date类型

DECLARE

  @start AS DATE = '',

  @end   AS DATE = '';

SELECT DATEADD(day, n, @start) AS dt

FROM dbo.GetNums(0, DATEDIFF(day, @start, @end)) AS Nums;

GO  

DECLARE

  @start AS DATETIME2 = '2012-02-12 00:00:00.0000000',

  @end   AS DATETIME2 = '2012-02-18 12:00:00.0000000';

SELECT DATEADD(hour, n*12, @start) AS dt

FROM dbo.GetNums(0, DATEDIFF(hour, @start, @end)/12) AS Nums;

GO

[sql] view plain copy

--T-SQL

CREATE TABLE #NumberSequense(RN INT);

DECLARE @i int;

SET @i = 1  

WHILE @i <= 1000

BEGIN

INSERT INTO #NumberSequense

SELECT @i;

SET @i = @i + 1

END  

SELECT *

  FROM #NumberSequense

 ORDER BY RN

DROP TABLE #NumberSequense;  

--T-SQL

CREATE TABLE #NumberSequense(RN INT);

DECLARE @i int;

SET @i = 1  

WHILE @i <= 1000

BEGIN

INSERT INTO #NumberSequense

SELECT @i;

SET @i = @i + 1

END  

SELECT *

  FROM #NumberSequense

 ORDER BY RN

DROP TABLE #NumberSequense;