欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-10 07:57:12
数字辅助表是一个连续整数的数列,通常用来实现多种不同的查询任务。大多分两类:足够大物理数字表和表函数,前者可以称为静态的,后者可以称为动态且按需生产。 物理数字表...

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

物理数字表

    物理数字表通常存在一个物理表,表记录相对足够大,相关的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

执行后的查询结果如下:

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

 表函数

    表函数实现使用交叉连接和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

执行后的查询结果如下:

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

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

参考清单列表

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

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