树型数据表的处理[找父,找子]

--环境
--win 2k p
--sql server 2000

-- 创建测试数据
if 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 PRIMARY KEY,[pid] int,name nvarchar(20))
INSERT [tb] SELECT  1,0,N'中国'
UNION  ALL  SELECT  2,0,N'美国'
UNION  ALL  SELECT  3,0,N'加拿大'
UNION  ALL  SELECT  4,1,N'北京'
UNION  ALL  SELECT  5,1,N'上海'
UNION  ALL  SELECT  6,1,N'江苏'
UNION  ALL  SELECT  7,6,N'苏州'
UNION  ALL  SELECT  8,7,N'常熟'
UNION  ALL  SELECT  9,6,N'南京'
UNION  ALL  SELECT 10,6,N'无锡'
UNION  ALL  SELECT 11,2,N'纽约'
UNION  ALL  SELECT 12,2,N'旧金山'
UNION  ALL  SELECT 13,8,N'新城区'
GO

-- 查询指定id的所有父
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getParent]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getParent]
GO

CREATE FUNCTION [f_getParent](@id int)
RETURNS @re TABLE(id int,pid int,level int)
AS
begin
declare @level int
set @level = 1
declare @pid int
select @pid = pid from tb where id = @id
insert @re
select id,pid,@level from tb where id = @pid
while @@rowcount > 0
begin
 set @level = @level + 1
 select @pid = pid from tb where id = @pid
 insert @re
 select id,pid,@level from tb where id = @pid
end
return
end
GO
--调用(查询所有的父)
SELECT * from f_getParent(8)
GO

--查询指定ID所有子
--创建用户定义函数
--下函数来自CSDN: libin_ftsafe(子陌红尘)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getChild]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getChild]
GO

create function f_getChild(@ID VARCHAR(10))
returns @t table(ID int,PID int,Level INT)
as
begin
    declare @i int,@ret varchar(8000)
    set @i = 1
    insert into @t select ID,PID,@i from tb where PID = @ID   
    while @@rowcount<>0  --递归边界条件
    begin
        set @i = @i + 1       
        insert into @t
        select
            a.ID,a.PID,@i
        from
            tb a,@t b
        where
            a.PID=b.ID and b.Level = @i-1 --level的作用体现
    end
    return
end
go
--调用(查询所有的子)
select * from f_getChild(6)
go