implement of tree menu on database

this article post on CSDN Community.
Learning......

i f exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO

--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
go

select * from tb

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO

/*--树形数据处理

查询指定id的所有儿子

--邹建 2003-12(引用请保留此信息)--*/

/*--调用示例

--调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
--*/
create function f_cid(@id int)
returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[id],@l
from [tb] a,@re b
where a.[pid]=b.[id] and b.[level]=@l-1
end
/*--如果只显示最明细的子(下面没有子),则加上这个删除
delete a from @re a
where exists(
select 1 from [tb] where [pid]=a.[id])
--*/
return
end
go

--调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
go



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_pid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_pid]
GO

/*--树形数据处理


查询指定id的所有父

--邹建 2003-12(引用请保留此信息)--*/

/*--调用示例

--调用(查询所有的父)
select a.* from [tb] a,f_pid(7)b where a.[id]=b.[id]
--*/
create function f_pid(@id int)
returns @re table([id] int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select [pid],@l from [tb] where [id]=@id and [pid]<>0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.[pid],@l
from [tb] a,@re b
where a.[id]=b.[id] and b.[level]=@l-1 and a.[pid]<>0
end
return
end
go

--调用(查询所有的父)
select a.* from [tb] a,f_pid(7)b where a.[id]=b.[id]
go