取指定数值段的数字表

最新推荐文章于 2025-02-22 18:23:40 发布

qq874655551

最新推荐文章于 2025-02-22 18:23:40 发布

阅读量357

点赞数

分类专栏： sqlserver 常用sql语句文章标签：取指定数值段的数字表

sqlserver 同时被 2 个专栏收录

4 篇文章

订阅专栏

常用sql语句

4 篇文章

订阅专栏

本文介绍了一种使用SQL生成虚拟辅助数字表的方法，该表能够快速生成连续整数序列，适用于不同场景需求。通过递归交叉连接及优化处理，实现高效灵活的数字序列生成。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考：虚拟辅助数字表
译文：
辅助数字表是一个辅助表，其中包含从1或其他数字开始的整数序列。这是一个非常方便的帮助表，我用来解决许多不同类型的问题。如果您不能或不想生成永久性表，则可以用它非常有效地生成一个虚拟的表。诀窍是使用交叉连接。您从一个带有两行的虚拟表开始：

WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1)--2条记录

然后，您在此表的两个实例之间执行交叉连接：

  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),--2条记录
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B)--L1=L0自交叉 4条记录

然后, 您在最后一个表的两个实例之间执行交叉连接：

  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),--2条记录
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),L1=L0自交叉 4条记录
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B)L2=L1自交叉 16条记录

并且在应用这样的交叉连接五次之后，你有一个2 ^ 2 ^5（4,294,967,296）行的表：

  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  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)

要产生实际的数字序列，可以使用ORDER BY（SELECT NULL）的ROW_NUMBER函数，让优化程序知道它不需要对数据进行排序。

您可以根据此代码创建表函数，并通过传递输入参数来请求一定数量的数字。棘手的一部分是提出一个解决方案，一旦产生了所要求的数量，就停止处理笛卡尔积，而不是总是试图产生全部40亿个。直到最近，我使用了以下解决方案：

IF OBJECT_ID('dbo.GetNums') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  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 n FROM L5)
  SELECT n FROM Nums WHERE n <= @n;
GO

要返回10个数字，只需要传递参数10：

SELECT n FROM dbo.GetNums(10) AS Nums;

执行后输出如下结果:

该代码很快执行完成，并在10行生成后停止处理。如果您查看为此查询生成的执行计划，您将注意到负责在正确点停止处理的Top操作符。但是，我注意到，在某些情况下，优化器会删除Top操作符，并且实际上会尝试在过滤之前生成所有40亿行。这是一个这样的例子：

WITH Primes(p) AS
(
  SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 5
  UNION ALL SELECT 7
)
SELECT *
FROM Primes
  CROSS APPLY dbo.GetNums(Primes.p) AS Nums;--要很久

最近我修改了使用TOP选项而不是WHERE过滤器的解决方案：

IF OBJECT_ID('dbo.GetNums') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  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 n FROM L5)
  SELECT TOP (@n) n FROM Nums ORDER BY n;
GO

对于这种解决方案，优化器总是似乎使用Top操作符，并在正确的位置(参数指定返回数)停止处理。使用新版本重新创建功能后，请尝试以下操作:

WITH Primes(p) AS
(
  SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 5
  UNION ALL SELECT 7
)
SELECT *
FROM Primes
  CROSS APPLY dbo.GetNums(Primes.p) AS Nums;--很快

这一次很快就能完成.

Cheers,

引用：取指定数值段的数字表

--select * from dbo.GetNums(1, 100000)
--PRE 2012
/* Based on the article "Virtual Auxiliary Table of Numbers" by Itzik Ben-Gan at http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers */
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

--SELECT * FROM dbo.GetNums1(1, 1000000)
--2012 ALT1
/* Based on the article "Virtual Auxiliary Table of Numbers" by Itzik Ben-Gan at http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers */
CREATE FUNCTION dbo.GetNums1(@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


--SELECT * FROM dbo.GetNums2(1000000)
--2012 ALT2 ADD MORE REFERENCES TO SYS.OBJECTS AS NEEDED
CREATE FUNCTION  dbo.GetNums2(@N AS BIGINT) RETURNS TABLE
AS
RETURN
SELECT TOP (@N) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS I
    FROM SYS.OBJECTS S1
    CROSS JOIN SYS.OBJECTS S2
    CROSS JOIN SYS.OBJECTS S3
    CROSS JOIN SYS.OBJECTS S4
    CROSS JOIN SYS.OBJECTS S5
GO