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'