SQL 2000ºÍ2005 Ê÷Ðεݹ鷨С»ã×Ü ÊÕ²Ø

--²âÊÔÊý¾Ý
if OBJECT_ID('tb') is not null
drop table tb
go
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'ɽ¶«Ê¡'
UNION ALL SELECT '002','001','ÑĮ̀ÊÐ'
UNION ALL SELECT '004','002','ÕÐÔ¶ÊÐ'
UNION ALL SELECT '003','001','ÇൺÊÐ'
UNION ALL SELECT '005',NULL ,'ËÄ»áÊÐ'
UNION ALL SELECT '006','005','ÇåÔ¶ÊÐ'
UNION ALL SELECT '007','006','С·ÖÊÐ'
GO
--2000µÄ·½·¨
--²éѯָ¶¨½Úµã¼°ÆäËùÓÐ×Ó½ÚµãµÄº¯Êý
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
declare @Level int
set @level=1
insert @t_level select @id,@level
while @@rowcount>0
begin
    set @level=@level+1
    insert @t_Level select tb.id,@level
     from tb join @t_level t on tb.pid=t.id
     where t.level+1=@level
   end
   return
end
select tb.*
from tb join dbo.f_cid('002') b
on tb.ID=b.id
/*
ID   PID  Name
---- ---- ----------
002  001  ÑĮ̀ÊÐ
004  002  ÕÐÔ¶ÊÐ
*/
go
--2005µÄ·½·¨£¨CTE£©
declare @n varchar(10)
set @n='002'
;with
jidian as
(
select * from tb where ID=@n
  union all
 select t.* from jidian j join tb t on j.ID=t.PID
)
select * from jidian
go
/*
ID   PID  Name
---- ---- ----------
002  001  ÑĮ̀ÊÐ
004  002  ÕÐÔ¶ÊÐ
*/
go
--²éÕÒÖ¸¶¨½ÚµãµÄËùÓи¸½Úµã(±ê×¼Ê÷ÐÎ,¼´Ò»¸ö×Ó½ÚµãÖ»ÓÐÒ»¸ö¸¸½Úµã)
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID from tb
WHERE ID=@ID
  AND PID IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
  INSERT @t_Level SELECT @ID
  SELECT @ID=PID from tb
  WHERE ID=@ID
   AND PID IS NOT NULL
END
RETURN
END
select tb.*
from tb join dbo.f_Pid('004') b
on tb.ID=b.id
/*
ID   PID  Name
---- ---- ----------
001  NULL ɽ¶«Ê¡
002  001  ÑĮ̀Ê