化解32层递归限制
来源:互联网 发布:淘宝站内免费推广 编辑:程序博客网 时间:2024/05/17 00:19
--2005化解32层递归限制,2000可用循环替代或用(游标while加break递归自己用一个变量传参)
if object_id('Tree','U') is not null
drop table [Tree]
go
CREATE TABLE [dbo].[Tree](
[ID] [bigint] identity,
[Parent] as (ID-1),
[Name] as ('Name'+rtrim(ID))
)
go
declare @i int
set @i=35
while @i>0
begin
insert [tree] default values
set @i=@i-1
end
--生成格式:
/**//*
ID Parent Name
-------------------- -------------------- ----------------------------
1 0 Name1
2 1 Name2
3 2 Name3
4 3 Name4
5 4 Name5
6 5 Name6
7 6 Name7
8 7 Name8
9 8 Name9
10 9 Name10
................................................
................................................
31 30 Name31
32 31 Name32
33 32 Name33
34 33 Name34
35 34 Name35
*/
go
if object_id('F_BOM','FN') is not null
drop function F_BOM
go
create function F_BOM(@ID int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000),@Name nvarchar(20)
lab:
set @Name =(select Name from Tree where ID=@ID)
select @ID=Parent from Tree where ID=@ID
if @Name is not null
begin
set @s=@Name+isnull('-'+@s,'')
goto lab
end
return @s
end
go
if object_id('F_BOM2','FN') is not null
drop function F_BOM2
go
create function F_BOM2(@ID int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
while exists(select 1 from Tree where ID=@ID)
select @s=Name+isnull('-'+@s,''),@ID=Parent from Tree where ID=@ID
return @s
end
go
--SQL2005:
if object_id('F_BOM3','FN') is not null
drop function F_BOM3
go
create function F_BOM3(@ID int)
returns nvarchar(max)
as
begin
declare @s nvarchar(max);
with BOM(ID,Name,parent,lev)
as
(
select ID,cast(Name as nvarchar(max)),parent,0 from tree where ID=@ID
union all
select
a.ID,cast(a.Name+'-'+b.Name as nvarchar(max)),a.Parent,b.lev+1
from
Tree a
join
BOM b on a.ID=b.Parent
)
select @s=Name from BOM where lev=(select max(lev) from BOM)
option(maxrecursion 0) -----------設置遞歸次數0為無限制,默認為100層
return @s
end
go
select dbo.F_BOM(35)
select dbo.F_BOM2(35)
select dbo.F_BOM3(35)
/**//*
Name1-Name2-Name3-Name4-Name5-Name6-Name7-Name8-Name9-Name10-
Name11-Name12-Name13-Name14-Name15-Name16-Name17-Name18-Name19-Name20-
Name21-Name22-Name23-Name24-Name25-Name26-Name27-Name28-Name29-Name30-
Name31-Name32-Name33-Name34-Name35
*/
if object_id('Tree','U') is not null
drop table [Tree]
go
CREATE TABLE [dbo].[Tree](
[ID] [bigint] identity,
[Parent] as (ID-1),
[Name] as ('Name'+rtrim(ID))
)
go
declare @i int
set @i=35
while @i>0
begin
insert [tree] default values
set @i=@i-1
end
--生成格式:
/**//*
ID Parent Name
-------------------- -------------------- ----------------------------
1 0 Name1
2 1 Name2
3 2 Name3
4 3 Name4
5 4 Name5
6 5 Name6
7 6 Name7
8 7 Name8
9 8 Name9
10 9 Name10
................................................
................................................
31 30 Name31
32 31 Name32
33 32 Name33
34 33 Name34
35 34 Name35
*/
go
if object_id('F_BOM','FN') is not null
drop function F_BOM
go
create function F_BOM(@ID int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000),@Name nvarchar(20)
lab:
set @Name =(select Name from Tree where ID=@ID)
select @ID=Parent from Tree where ID=@ID
if @Name is not null
begin
set @s=@Name+isnull('-'+@s,'')
goto lab
end
return @s
end
go
if object_id('F_BOM2','FN') is not null
drop function F_BOM2
go
create function F_BOM2(@ID int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
while exists(select 1 from Tree where ID=@ID)
select @s=Name+isnull('-'+@s,''),@ID=Parent from Tree where ID=@ID
return @s
end
go
--SQL2005:
if object_id('F_BOM3','FN') is not null
drop function F_BOM3
go
create function F_BOM3(@ID int)
returns nvarchar(max)
as
begin
declare @s nvarchar(max);
with BOM(ID,Name,parent,lev)
as
(
select ID,cast(Name as nvarchar(max)),parent,0 from tree where ID=@ID
union all
select
a.ID,cast(a.Name+'-'+b.Name as nvarchar(max)),a.Parent,b.lev+1
from
Tree a
join
BOM b on a.ID=b.Parent
)
select @s=Name from BOM where lev=(select max(lev) from BOM)
option(maxrecursion 0) -----------設置遞歸次數0為無限制,默認為100層
return @s
end
go
select dbo.F_BOM(35)
select dbo.F_BOM2(35)
select dbo.F_BOM3(35)
/**//*
Name1-Name2-Name3-Name4-Name5-Name6-Name7-Name8-Name9-Name10-
Name11-Name12-Name13-Name14-Name15-Name16-Name17-Name18-Name19-Name20-
Name21-Name22-Name23-Name24-Name25-Name26-Name27-Name28-Name29-Name30-
Name31-Name32-Name33-Name34-Name35
*/
- 化解32层递归限制
- 突破SQL2005CTE递归最大100层的限制
- 递归调用的次数限制
- sql 无限层递归函数
- javascript 鼠标拖动层。限制拖动范围
- ArcGIS服务器的feature图层限制
- javascript Deferred和递归次数限制
- 限制递归次数,避免无限循环
- 二叉树遍历:递归+非递归+逐层遍历
- 树的递归,非递归,层序遍历
- 二叉树遍历(递归,非递归,层序)
- 00604: 递归 SQL 层 1 出现错误
- 递归按层遍历二叉树算法
- 模拟层实现限制下拉框显示个数
- Geoserver项目开发(三) -图层加载限制(一)
- Geoserver项目开发(三) -图层加载限制(二)
- 小程序突破五层限制的方法
- 微信小程序突破五层限制的方法
- VS2005/SQL2005等原版镜像高速下载
- ActiveBPEL Engine和designer
- Server.Redirect(url),拋出"正在中止线程。"的解決方法
- 获取客户端IP
- emacs使用技巧(updating.......)
- 化解32层递归限制
- 专栏公告
- 软件测试管理--第二章 2.1节
- NetBeans IDE 5.5 J2SE(TM) Development Kit (JDK) 版本 5.0 下载
- atleap0.51 安装指南
- 计算机编程:一门艺术
- 谈谈IT行业的收入和一些生存之道(整理版) (转载)
- .NET Framework事件设计准则
- 杰克逊