SQL Server 实现数字辅助表实例代码
程序员文章站
2022-06-24 20:04:10
数字辅助表是一个连续整数的数列,通常用来实现多种不同的查询任务。大多分两类:足够大物理数字表和表函数,前者可以称为静态的,后者可以称为动态且按需生产。
物理数字表...
数字辅助表是一个连续整数的数列,通常用来实现多种不同的查询任务。大多分两类:足够大物理数字表和表函数,前者可以称为静态的,后者可以称为动态且按需生产。
物理数字表
物理数字表通常存在一个物理表,表记录相对足够大,相关的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 有关书籍的作者)
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
上一篇: SQL分页查询方式汇总