oracle递归查询

strat with 指定层次开始的条件，即是说满足这个条件的行即可以做为层次树的最顶层 
connect by prior指层之间的关联条件，即什么样的行是上层行的子行（自连接条件）

 

--查询所有子节点
SELECT *
FROM hrmdepartment
where canceled = 0
START WITH departmentname ='智能管理部'
CONNECT BY PRIOR id=supdepid

 

 

--查询所有父节点
SELECT *
FROM hrmdepartment
where canceled = 0
START WITH departmentname ='开发模块'
CONNECT BY PRIOR supdepid=id

 

 

--查询指定节点的，根节点
SELECT hd.*,connect_by_root(id),connect_by_root(departmentname)
FROM hrmdepartment hd
where canceled = 0
START WITH departmentname ='智能管理部'
CONNECT BY PRIOR id=supdepid

 

 

 

--递归路径
SELECT hd.*,sys_connect_by_path(departmentname,'->') namepath,LEVEL
FROM hrmdepartment hd
where canceled = 0
START WITH departmentname ='智能管理部'
CONNECT BY PRIOR id=supdepid