有这样一个表:DEPT
ID ID
PARENT_ID 父部门ID,顶级部门的父部门为null
DEPT_NAME 部门名称
如果要从顶级部门开始递归所有部门:
select level,a.* from DEPT a start with a.PARENT_ID is null
connect by prior a.ID=a.PARENT_ID
level:层次
start_with:入口部门
prior:前序遍历
ID=PARENT_ID:从入口部门开始往下递归
PARENT_ID=ID:从入口部门开始往上递归
了解了上述规则,则不难写出从某部门开始的所有下级部门:
select level,a.* from DEPT a start with a.ID = 3
connect by prior a.ID=a.PARENT_ID
从某部门开始的上级部门:
select level,a.* from DEPT a start with a.ID = 3
connect by prior a.PARENT_ID = a.ID
如果要在以上结果中进行排除,如排除当前部门,即某部门的上级部门(注意where筛选在递归结果之后进行):
select level,a.* from DEPT a
where a.ID != 3
start with a.ID = 3
connect by prior a.PARENT_ID = a.ID
如果还想按层级显示部门名称,如:
ID NEW_DEPT_NAME DEPT_NAME
3 部门A/部门A-1/部门A-1-1 部门A-1-1
可以使用如下语句:
select level,
ltrim(sys_connect_by_path(a.DEPT_NAME,'',''),'','') NEW_DEPT_NAME,
a.*
from DEPT a
start with a.ID = 3
connect by prior a.ID=a.PARENT_ID