SQL Server 数字辅助表（数列）实现方案

远山来客

发布时间：2026-10-13 | 4人浏览过

原创

数字辅助表在 SQL Server 开发中扮演着重要角色，它能够快速生成连续的整数列，广泛应用于日期扩展、数据填充和分页逻辑。针对不同的存储需求和性能要求，开发者可以选择使用预先建立的物理表或按需生成的表值函数。

结果与准备

数字辅助表通常分为两类：物理数字表和表函数。前者为静态结构，适合高频访问且需要持久化的场景；后者利用递归公共表表达式（CTE）动态计算，适合临时性或大规模数据扫描，不占用额外的磁盘空间。

真实操作顺序

第 1 步：创建物理数字表

目的：建立一个包含连续整数的静态表，作为数据字典或连接的基础。

操作入口：SQL Server Management Studio 查询窗口。

代码说明：通过删除旧对象、创建新表并利用 master.dbo.spt_values 系统视图填充数据。该视图是 SQL Server 内置的系统表，包含大量行，适合快速获取几千到上万个连续数字。

IF OBJECT_ID(N'dbo.Nums', 'U') IS NOT NULL
BEGIN
  DROP TABLE dbo.Nums;
END
GO
 
CREATE TABLE dbo.Nums 
(
  Num INT NOT NULL,
  CONSTRAINT PK_U_CL_Nums_Num PRIMARY KEY CLUSTERED
  (
    Num ASC
  ) 
);
GO
 
INSERT INTO dbo.Nums (Num)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM master.dbo.spt_values;
GO

验证状态：执行以下语句，确认表中有数据返回：

SELECT Num FROM dbo.Nums;

执行后应看到从起始值开始的连续整数列表。

第 2 步：创建动态表值函数（标准版）

目的：构建一个可复用的表值函数，根据传入的低值和界限，动态生成指定范围的整数序列。

核心原理：使用多层 CTE 进行自连接（Cross Join）。每一层的数据行数翻倍，L5 层级理论上可产生超过 40 亿行，足以满足绝大多数业务需求。

参数设置：@bintLow 为起始数，@bintHigh 为结束数。

IF OBJECT_ID(N'dbo.ufn_GetNums', N'IF') IS NOT NULL
BEGIN
  DROP TABLE dbo.ufn_GetNums;
END
GO
 
-- 功能: 获取指定范围的数字数列
-- 说明: 利用交叉连接逐层倍增行数
CREATE FUNCTION dbo.ufn_GetNums
(
  @bintLow BIGINT,
  @bintHigh BIGINT
) RETURNS TABLE
AS
RETURN 
  WITH
    L0 AS (SELECT c FROM (VALUES(1), (1)) AS LO(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS T CROSS JOIN L0 AS T2),
    L2 AS (SELECT 1 AS c FROM L1 AS T CROSS JOIN L1 AS T2),
    L3 AS (SELECT 1 AS c FROM L2 AS T CROSS JOIN L2 AS T2),
    L4 AS (SELECT 1 AS c FROM L3 AS T CROSS JOIN L3 AS T2),
    L5 AS (SELECT 1 AS c FROM L4 AS T CROSS JOIN L4 AS T2),
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM L5)
 
  SELECT TOP (@bintHigh - @bintLow + 1) @bintLow + RowNum - 1 AS Num
  FROM Nums
  ORDER BY RowNum ASC;
GO

第 3 步：测试函数调用效果

目的：验证生成的函数能否正确输出指定区间内的连续数字。

动作：调用 dbo.ufn_GetNums 并指定范围为 11 到 20。

SELECT Num
FROM dbo.ufn_GetNums(11, 20);
GO

预期结果：返回 11, 12, ..., 20 共 10 条记录。

第 4 步：优化 SQL Server 2012+ 版本写法

背景：SQL Server 2012 引入了 OFFSET...FETCH 语法，相比 TOP 更具灵活性，尤其在与子查询结合时。

操作差异：将原函数中的 TOP ... ORDER BY 替换为标准的偏移取回语法。

-- 替代 ufn_GetNums 的高版本写法
CREATE FUNCTION dbo.ufn_GetNums2
(
  @bintLow BIGINT,
  @bintHigh BIGINT
) RETURNS TABLE
AS
RETURN 
  WITH
    L0 AS (SELECT c FROM (VALUES(1), (1)) AS LO(c)),
    -- 中间层级省略，结构同上...
    L5 AS (SELECT 1 AS c FROM L4 AS T CROSS JOIN L4 AS T2),
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM L5)
 
  SELECT @bintLow + RowNum - 1 AS Num
  FROM Nums
  ORDER BY RowNum ASC
  OFFSET 0 ROWS FETCH FIRST @bintHigh - @bintLow + 1 ROWS ONLY;
GO