select deptId,parentDeptId,deptName,sys_connect_by_path(deptName,',') path
from tb_dept
connect by deptId =prior parentDeptId
start with parentDeptId is null
from tb_dept
connect by deptId =prior parentDeptId
start with parentDeptId is null
调试欢乐多
with t as
(select 'A' ID, 'B' PID
FROM DUAL
UNION ALL
select 'B' ID, 'C' PID
FROM DUAL
UNION ALL
select 'C' ID, 'D' PID
FROM DUAL
UNION ALL
select 'D' ID, 'E' PID
FROM DUAL
UNION ALL
select 'E' ID, 'F' PID
FROM DUAL
UNION ALL
select 'F' ID, NULL PID
FROM DUAL)select path
from (select ltrim(sys_connect_by_path(ID, ','), ',') path,
CONNECT_BY_ISLEAF leaf
from T
start with pid is null
connect by pid = prior ID)
where leaf = 1
with t as
(select 'A' ID, 'B' PID
FROM DUAL
UNION ALL
select 'B' ID, 'C' PID
FROM DUAL
UNION ALL
select 'C' ID, 'D' PID
FROM DUAL
UNION ALL
select 'D' ID, 'E' PID
FROM DUAL
UNION ALL
select 'E' ID, 'F' PID
FROM DUAL
UNION ALL
select 'F' ID, NULL PID
FROM DUAL)
select listagg(id,',')within group(order by level desc)
from t connect by prior id=pid start with pid is null
select listagg(id,',')within group(order by level)
from t T1 connect by prior pid=id
start with not exists (select 1 from t where pid=T1.ID)
GROUP BY CONNECT_BY_ROOT(ID)