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可能得到结果集。
页:
[1]