SQL 根据父节点查询所有子节点

--查找子节点   
  
createtable Info   
(   
  ID char(3),   
  PID char(3),   
  [Name]varchar(20)   
)   
insertinto Info select'001',null,'山东省'   
unionallselect'002','001','烟台市'   
unionallselect'004','002','招远市'   
unionallselect'003','001','青岛市'   
unionallselect'005',null,'四会市'   
unionallselect'006','005','清远市'   
unionallselect'007','006','小分市'   
  
createfunction dbo.f_id(@idchar(3))   
returns@tb_leveltable(id char(3),[level]int)   
as   
begin   
 declare@levelint   
  set@level=0  
 insertinto@tb_levelselect@id,@level   
 while@@rowcount>0   
 begin   
   set@level=@level+1;   
   insertinto@tb_levelselect I.ID,@levelfrom Info I,@tb_level tb where I.PID=tb.ID and[level]=@level-1   
 end   
return   
end    
  
select I.*from Info I,dbo.f_id('002') F where I.ID=F.ID   
  
ID   PID  Name   
---- ---- --------------------   
002001  烟台市   
004002  招远市