数字辅助表

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+Nums.rownum-1 AS n FROM Nums ORDER BY Nums.rownum OFFSET 0 ROWS FETCH FIRST @high-@low+1 ROWS ONLY;

　　SELECT * FROM dbo.GetNums(11,20)

 

n
11
12
13
14
15
16
17
18
19
20