虚拟数字辅助表

虚拟数字辅助表

1. 概念

概念来源于《Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions》
代码如下：

GO
/****** Object:  UserDefinedFunction [dbo].[GetNums]    Script Date: 2018/5/25 9:20:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetNums]
(   
    @Low BIGINT,
    @High BIGINT
)
RETURNS TABLE 
AS
RETURN 
    WITH
        L0 AS (SELECT C=1 FROM (VALUES(1),(1)) AS L0(C)),
        L1 AS (SELECT C=1 FROM L0 A CROSS JOIN L0 B),
        L2 AS (SELECT C=1 FROM L1 A CROSS JOIN L1 B),
        L3 AS (SELECT C=1 FROM L2 A CROSS JOIN L2 B),
        L4 AS (SELECT C=1 FROM L3 A CROSS JOIN L3 B),
        L5 AS (SELECT C=1 FROM L4 A CROSS JOIN L4 B),
        Nums AS (SELECT RowNum=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM L5)

     SELECT Num=@Low + RowNum - 1 FROM Nums ORDER BY RowNum ASC
     OFFSET 0 ROWS FETCH FIRST @High - @Low + 1 ROWS ONLY;
GO

调用：

SELECT * FROM [dbo].[GetNums](1, 10)

结果：

2. 案例

to be continued