在sql中使用递归查询部门的全路径

select reverse(SYS_CONNECT_BY_PATH(reverse(unit_name),'/'))
from "UNIT"
where  unit_ID ='根节点的unit_id'
START WITH unit_id = '当前节点的unit_id'
CONNECT BY unit_id = PRIOR SUPERIOR_UNIT_ID

结果为  阿里巴巴/运营部/行政处/

当我们要查询所有人的类似上述结果样式的详细组织情况的时候

只需要将人员表与组织表相关联，然后在select中加入此select查询结果作为组织名称即可

类似

SELECT
pi.uuid      ,
pi.name      ,
pi.sex       ,
(select reverse(SYS_CONNECT_BY_PATH(reverse(unit_name),'/'))
from "UNIT"
where  unit_ID ='根节点的unit_id'
START WITH unit_id = '当前节点的unit_id'
CONNECT BY unit_id = PRIOR SUPERIOR_UNIT_ID) as unit_name,
pu.superior_unit_name,
pu.unit_name pname,
pu.superior_unit_id,
pi.post_job  ,
pi.order_num,
pi.UNIT_ID,
pi.USER_ID
from
USER pi
left join
UNIT pu on pi.UNIT_ID=pu.UNIT_ID
where
pu.invalid='0'
and pi.invalid='0'