SQL Server 实现数字辅助表实例代码

数字辅助表是一个连续整数的数列，通常用来实现多种不同的查询任务。大多分两类：足够大物理数字表和表函数，前者可以称为静态的，后者可以称为动态且按需生产。

物理数字表

    物理数字表通常存在一个物理表，表记录相对足够大，相关的t-sql代码如下：

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

注意：如何填充物理数字表的方法很多，为了演示作用使用了一种。

测试的t-sql代码如下：

1 select num
2 from dbo.nums;
3 go

执行后的查询结果如下：

 表函数

    表函数实现使用交叉连接和cte，sql server 2005和以上版本的t-sql代码如下：

if object_id(n'dbo.ufn_getnums', n'if') is not null
begin
  drop table dbo.ufn_getnums;
end
go
 
--==================================
-- 功能: 获取指定范围的数字数列
-- 说明: 交叉最后层级的cte得到的数据行：在l级（从0开始计数）得到的行的总数为2^2^l。
--    例如：在5级就会得到4 294 967 596行。5级的cte提供了超过40亿的行。
-- 作者: xxx
-- 创建: yyyy-mm-dd
-- 修改: yyyy-mm-dd xxx 修改内容描述
--==================================
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

    sql server 2012增加了有关分页的新特性，相关的t-sql代码如下:

if object_id(n'dbo.ufn_getnums2', n'if') is not null
begin
  drop table dbo.ufn_getnums2;
end
go
 
--==================================
-- 功能: 获取指定范围的数字数列
-- 说明: 交叉最后层级的cte得到的数据行：在l级（从0开始计数）得到的行的总数为2^2^l。
--    例如：在5级就会得到4 294 967 596行。5级的cte提供了超过40亿的行。 
-- 作者: xxx
-- 创建: yyyy-mm-dd
-- 修改: yyyy-mm-dd xxx 修改内容描述
--==================================
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)),
    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 @bintlow + rownum - 1 as num
  from nums
  order by rownum asc
  offset 0 rows fetch first @binthigh - @bintlow + 1 rows only;
go

以函数ufn_getnums为例，演示相关的效果。获取指定范围的数字序列的t-sql代码如下：

select num
from dbo.ufn_getnums(11, 20);
go

执行后的查询结果如下：

 

博友如有其他更好的解决方案，也请不吝赐教，万分感谢。

参考清单列表

1、《microsoft sql server 2012 high-performance t-sql using window functions》 作者 itzik ben-gan（美国）（sql server inside 有关书籍的作者）

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！