T-sql 求注释?

[大力水手]

set nocount on;
use tempdb;
go
if OBJECT_ID('dbo.nums')is not null
	drop table dbo.nums;
go
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; --第二行1+1=2插入一个2  下次2+1,2+4 一个3一个4  当@rc=4  1+4,2+4,3+4
	set @rc=@rc*2;
end
insert into dbo.nums
	select n+@rc from dbo.nums where n+@rc<=@max;


--- 生成日期序列
declare @s as datetime,@e as datetime;
set @s='20060101';
set @e='20061231';
--select DATEDIFF(day,@s,@e)+1
select @s+n-1 as dt from dbo.nums where n<=DATEDIFF(day,@s,@e)+1;
select @s-1

--udf返回一个辅助数字表
create function dbo.fn_nums(@n as bigint) returns table
as
return
	with    
	lo as(select 1 as c union all select 1),
	l1 as(select 1 as c from lo as a,lo 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

---该函数创建后,可以想使用nums表一样使用它
declare @s as datetime,@e as datetime;
set @s='20060101'
set @e='20061231'
select @s + n - 1 as dt from dbo.fn_nums(DATEDIFF(DAY,@s,@e)+1) as nums;



不理解这一段代码

create function dbo.fn_nums(@n as bigint) returns table
as
return
	with    
	lo as(select 1 as c union all select 1),
	l1 as(select 1 as c from lo as a,lo 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

设个函数的with l0,l1,l2,l3,l4,l5   和 c  , a ,b 各代表什么意识...越详细越好?谢谢

回答就有分得!
在线等

[kevn]

这样回答有分吗？

[大力水手]

看你8格子的份上,,,给1分,,,接贴时给你

[AcHerat]

    with    
    lo as(select 1 as c union all select 1),
    l1 as(select 1 as c from lo as a,lo as b),
    l2 as(select 1 as c from l1 as a,l1 as b)

select * from l2

/**

with ...  是SQL2005及以上版本中的一个特殊用法，类似于临时表，不过只是一次性的查询

select 1 as c from lo as a,lo as b

这样楼主看出来什么，将lo当做两个表，按笛卡尔积查询，lo记录数是2，两次全排列查询就是2的2次方，当然你也可以继续加几个 lo as d,lo as e 这样就是2的4次方，下边的依次类推，就能得到一个比较大的计数表

[--小F--]

select 1 as c from lo as a,lo as b

---c,a,b都是列名

这句话相当于
select 1 as c from lo a cross join lo b

[AcHerat]

    with    
    lo as(select 1 as c union all select 1),    -- 2的2次方
    l1 as(select 1 as c from lo as a,lo as b),  -- 2的4次方
    l2 as(select 1 as c from l1 as a,l1 as b),  -- 2的8次方
    l3 as(select 1 as c from l2 as a,l2 as b),  -- 2的16次方
    l4 as(select 1 as c from l3 as a,l3 as b),  -- 2的32次方
    l5 as(select 1 as c from l4 as a,l4 as b),  -- 2的64次方
    nums as (select row_number() over(order by c) as n from l5) -- 对l5表排序
    select n from nums where n<=@n  -- 返回符合表排序列小于参数的所有计数

[AcHerat]

[Quote=引用 4 楼 fredrickhu 的回复:]
SQL code
select 1 as c from lo as a,lo as b

---c,a,b都是列名

这句话相当于
select 1 as c from lo a cross join lo b
[/Quote]


select 1 as c from lo as a,lo as b

---c是列名
---a,b是表的别名

[东那个升]

自连接递归生成笛卡尔乘积

[东那个升]

再根据row_number（） 的生成NUM表

[--小F--]

[Quote=引用 6 楼 acherat 的回复:]
引用 4 楼 fredrickhu 的回复:
SQL code
select 1 as c from lo as a,lo as b

---c,a,b都是列名

这句话相当于
select 1 as c from lo a cross join lo b



select 1 as c from lo as a,lo as b

---c是列名
---a,b是表的别……
[/Quote]
恩 写错了

[大力水手]

还有没有要补充的! 没有就结贴了...理解了一部分 我在多写写