1.LEVEL 层级
2.CONNECT_BY_ISLEAF
是否为叶子节点,1为叶子节点,0为非叶子节点
select CONNECT_BY_ROOT("DeptName"),
rownum,
level l,
"DeptID",
"DeptName",
CONNECT_BY_ISLEAF
from pos_departmentnew
where "Flag" = 1
start with "DeptID" = 'JT'
connect by prior "DeptID" = "Parent";
3.SYS_CONNECT_BY_PATH("字段名", '分隔符') 对字段名从根节点开始按分隔符拼接各层的值
select CONNECT_BY_ROOT("DeptName"),
rownum,
substr(SYS_CONNECT_BY_PATH("DeptName", '/'),2) c,
level l,
"DeptID",
"DeptName",
CONNECT_BY_ISLEAF
from pos_departmentnew
where "Flag" = 1
start with "DeptID" = 'JT'
connect by prior "DeptID" = "Parent";
c 字段可能的结果为
小南国集团
小南国集团/华东区
小南国集团/华东区/世博店
小南国集团/华东区/世博店/上海世博1号店
小南国集团/华东区/世博店/上海世博2号店
小南国集团/华东区/江苏区域公司
小南国集团/华东区/江苏区域公司/水游城店
小南国集团/华东区/江苏区域公司/李公堤店
小南国集团/华东区/江苏区域公司/久光店
............