隶属关系查询解决方法

隶属关系查询
表如下
field1 field2
A1 a1
A1 a2
a1 aa1
a2 aa2
aa1 aaa1

field1是field2的上级，我希望查询所有field1的下级。 比如当field1为A1时，查询到field2为a1,a2,aa1,aa2,aaa1

[解决办法]

SQL code

-->Title:Generating test data-->Author:wufeng4552-->Date :2009-09-30 08:52:38set nocount onif object_id('tb','U')is not null drop table tbgocreate table tb(ID int, ParentID int)insert into tb select 1,0  insert into tb select 2,1  insert into tb select 3,1  insert into tb select 4,2  insert into tb select 5,3  insert into tb select 6,5  insert into tb select 7,6-->Title:查找指定節點下的子結點if object_id('Uf_GetChildID')is not null drop function Uf_GetChildIDgocreate function Uf_GetChildID(@ParentID int)returns @t table(ID int)asbegin   insert @t select ID from tb where ParentID=@ParentID   while @@rowcount<>0   begin      insert @t select a.ID from tb a inner join @t b      on a.ParentID=b.id and       not exists(select 1 from @t where id=a.id)   end returnendgoselect * from dbo.Uf_GetChildID(5)/*ID-----------67*/-->Title:查找指定節點的所有父結點if object_id('Uf_GetParentID')is not null drop function Uf_GetParentIDgocreate function Uf_GetParentID(@ID int)returns @t table(ParentID int)asbegin   insert @t select ParentID from tb where ID=@ID   while @@rowcount!=0   begin     insert @t select a.ParentID from tb a inner join @t b       on a.id=b.ParentID and        not exists(select 1 from @t where ParentID=a.ParentID)   end  returnendgoselect * from dbo.Uf_GetParentID(2)/*ParentID-----------10*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx参考资料