MSSQL finds sub-nodes and parent nodes

-->title:generating test data-->author:wufeng4552-->date:2009-09-30 08:52:38set nocount onif object_id (' TB ' , ' U ') is not a null drop table Tbgocreate table TB (ID int, parentid int) insert INTO TB select 1,0 INSERT INTO TB select 2,1 I Nsert into TB Select 3,1 Insert to TB Select 4,2 INSERT INTO TB Select 5,3 Insert to TB Select 6,5 INSERT INTO TB Sele CT 7,6-->title: Find sub-ends at specified nodes 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 [email protected] Whil e @ @rowcount <>0 begin insert @t Select a.ID from TB a inner join @t B on a.parentid=b.id and not ex Ists (select 1 from @t where id=a.id) Endreturnendgoselect * FROM dbo. Uf_getchildid (5)/*id-----------67*/-->title: Finds all the parent ends of the specified nodes 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 [email protected] while @ @rowcount!=0 begin insert @t Select A.PA    Rentid from TB a inner join @t B to A.id=b.parentid and NOT exists (select 1 from @t where Parentid=a.parentid) End Returnendgoselect * FROM dbo. Uf_getparentid (2)/*parentid-----------10*/

MSSQL finds sub-nodes and parent nodes