Sql Server 查找所有子节点

表名：collage_orgGroups

字段：主键：ID，父节点ID：parentID

 

函数：传入个ID，查找此ID下所有字节点ID

 

Create   Function   F_GetChildren(@Pid   int) 
Returns   @Tree   Table   (ID   Int) 
As 
Begin 
Insert   @Tree   Select   ID   From   collage_orgGroups   Where   parentID   =   @Pid  
While   @@Rowcount   >   0 
Insert   @Tree   Select   A.ID   From   collage_orgGroups   A   Inner   Join   @Tree   B   On   A.ParentID   =   B.ID   And   A.ID   Not   In   (Select   ID   From   @Tree) 
Return 
End 


select * from collage_orgGroups where ID IN(SELECT ID FROM dbo.F_GetChildren(2)) 

 

 

另外一种方式：

 

with myT as(
select ID ,parentID from collage_orgGroups
),
myT2 as(
select * from myT where parentID in(2,3)
union all
select myT.* from myT2 inner join myT
 on myT2.ID=myT.parentID
)
select ID,parentID from myT2

 

这个直接执行就行了。。就是不知道用JDBC可能得到结果集。