当前位置： 移动技术网 > IT编程>数据库>MSSQL > SqlServer 2005 T-SQL Query 学习笔记（4）

SqlServer 2005 T-SQL Query 学习笔记（4）

2017年12月08日  | 移动技术网IT编程  | 我要评论

奥斯曼中尉,精卫填海的神话故事,枫时代

比如，我要建立一个1，000，000行的数字表：

create table dbo.nums(n int not null primary key);
declare @max as int, @rc as int;
set @max = 1000000;
set @rc = 1;

insert into nums values(1);
while @rc * 2 <= @max
begin
insert into dbo.nums select n + @rc from dbo.nums;
set @rc = @rc * 2;
end

insert into dbo.nums
select n + @rc from dbo.nums where n + @rc <= @max;




这种方式非常巧妙，它并不是一个一个的循环插入，而是一次插入很多行，{1}，{2}，{3，4}，{5，6，7，8}。。。



为什么这样会快呢？

是因为它节省了跟比较其他可用解决方案进行比较和记录这些日志的时间。



然后，作者给了一个cte的递归的解决方案:

declare @n as bigint;
set @n = 1000000;

with nums as
(
select 1 as n
union all
select n + 1 from nums where n < @n
)
select n from nums
option(maxrecursion 0);--为了移除默认100的递归限制


有个更优的cte的解决方案，就是先生成很多行，然后用row_number进行计算，再选择row_number这列的值就可以了。

复制代码 代码如下:

declare @n as bigint;
set @n = 1000000;

with base as
(
select 1 as n
union all
select n + 1 from base where n < ceiling(sqrt(@n))
),
expand as
(
select 1 as c
from base as b1, base as b2
),
nums as
(
select row_number() over(order by c) as n
from expand
)
select n from nums where n <= @n
option(maxrecursion 0);


利用笛卡尔积进行不断的累加，达到了22n行。

最后，作者给出了一个函数，用于生成这样的数字表：
复制代码 代码如下:

create function dbo.fn_nums(@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, l0 as b),
l2 as(select 1 as c from l1 as a, l1 as b),
l3 as(select 1 as c from l2 as a, l2 as b),
l4 as(select 1 as c from l3 as a, l3 as b),
l5 as(select 1 as c from l4 as a, l4 as b),
nums as(select row_number() over(order by c) as n from l5)
select n from nums where n <= @n;
go

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网