MSSQL finds sub-nodes and parent nodes

-->Title:Generating test data -->Author:wufeng4552 -->Date :2009-09-30 08:52:38 set nocount on if object_id( ‘tb‘ , ‘U‘ ) is not null drop table tb go create 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_GetChildID go create function Uf_GetChildID(@ParentID int ) returns @t table (ID int ) as begin     insert @t select ID from tb where [email protected]     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 return end go select * from dbo.Uf_GetChildID(5) /* ID ----------- 6 7 */ -->Title:查找指定節點的所有父結點 if object_id( ‘Uf_GetParentID‘ ) is not null drop function Uf_GetParentID go create function Uf_GetParentID(@ID int ) returns @t table (ParentID int ) as begin     insert @t select ParentID from tb where [email protected]     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    return end go select * from dbo.Uf_GetParentID(2) /* ParentID ----------- 1 0 */

MSSQL finds sub-nodes and parent nodes