SQL Server 怎样生成序列号(虚拟数字辅助表)

</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 = '20120201',
  @end   AS DATE = '20120212';
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

--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;