# 树形结构的存储与查询

--2000化解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)                                        ----------- default 100
return @s
end
go

select dbo.F_BOM(35)
//select dbo.F_BOM2(35)
//select dbo.F_BOM3(35)

• 写评论