ÇóÒ»¼¶Áª²éѯSqlÓï¾ä - MS-SQL Server / »ù´¡Àà

ÎÒÓÐÒ»¸ö±í±£´æId,PId,NameÈý¸ö×Ö¶Î,·Ö±ð±íʾ±àºÅ,¸¸±àºÅºÍÃû³Æ,ÎÒҪʵÏÖµÄÊǸø¶¨Ò»¸ö±àºÅ,²é³ö¸Ã±àºÅϵÄËùÓкó´ú.
Èç:
Id PId Name
1 0 a
2 0 b
3 1 c
4 3 d
5 4 e
6 4 f
Èç¹ûÎÒÖ¸¶¨²éѯId=1,½á¹ûÒªÊÇ:3,4,5,6
Èç¹ûId=2,½á¹ûΪ¿Õ
Èç¹ûId=3,½á¹ûҪΪ:4,5,6
.....

---

SQL code:

/*
±êÌ⣺SQL SERVER 2000Öвéѯָ¶¨½Úµã¼°ÆäËùÓÐ×Ó½ÚµãµÄº¯Êý(±í¸ñÐÎʽÏÔʾ)
×÷Õߣº°®Ð¾õÂ޷ع»ª(Ê®°ËÄê·çÓê,ÊصñùɽѩÁ«»¨¿ª)
ʱ¼ä£º2008-05-12
µØµã£º¹ã¶«ÉîÛÚ
*/

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