connect by prior start with

connect by prior start with语句

ORACLE数据库的树型结构递归查询，如用在组织架构树型结构中取得某树节点的递归路径：

--子取父
select DID, GNAME from S_Group
CONNECT BY PRIOR PID = DID
START WITH DID='5'
Order By DID

--父取子
select DID, GNAME
from S_Group
CONNECT BY PRIOR DID = PID
START WITH DID='2'
Order By DID

--由人员得到所在顶级部门的名字
Select DID, GNAME From S_Group
Connect By Prior PID = DID
Start With DID=(Select S_User.PID From S_User Where S_User.DID='16')
Order By DID

2009/11/13 于南昌