¡¾×ª¡¿¹ØÓÚ"µÝ¹éÊ÷ÐβéѯSQL"µÄºÃÌû

-----------------------------------------------------------------------------------------------------------------------
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null  , '¹ã¶«Ê¡')
insert into tb values('002' , '001' , '¹ãÖÝÊÐ')
insert into tb values('003' , '001' , 'ÉîÛÚÊÐ')
insert into tb values('004' , '002' , '퓼')
insert into tb values('005' , '003' , 'ÂÞºþÇø')
insert into tb values('006' , '003' , '¸£ÌïÇø')
insert into tb values('007' , '003' , '±¦°²Çø')
insert into tb values('008' , '007' , 'Î÷ÏçÕò')
insert into tb values('009' , '007' , 'Áú»ªÕò')
insert into tb values('010' , '007' , 'ËɸÚÕò')
go
--²éѯָ¶¨½Úµã¼°ÆäËùÓÐ×Ó½ÚµãµÄº¯Êý
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @id , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.id , @level
    from tb a , @t_Level b
    where a.pid = b.id and b.level = @level - 1
  end
  return
end
go
--µ÷Óú¯Êý²éѯ001(¹ã¶«Ê¡)¼°ÆäËùÓÐ×Ó½Úµã
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id   pid  name      
---- ---- ----------
001  NULL ¹ã¶«Ê¡
002  001  ¹ãÖÝÊÐ
003  001  ÉîÛÚÊÐ
004  002  ÌìºÓÇø
005  003  ÂÞºþÇø
006  003  ¸£ÌïÇø
007  003  ±¦°²Çø
008  007  Î÷ÏçÕò
009  007  Áú»ªÕò
010  007  ËɸÚÕò
£¨ËùÓ°ÏìµÄÐÐÊýΪ 10 ÐУ©
*/
--µ÷Óú¯Êý²éѯ002(¹ãÖÝÊÐ)¼°ÆäËùÓÐ×Ó½Úµã
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id   pid  name      
---- ---- ----------
002  001  ¹ãÖÝÊÐ
004  002  ÌìºÓÇø
£¨ËùÓ°Ï