Oracle递归查询(二)

下行递归查询 河南省 深度、是否包含叶子节点

select id, name, parent_id, level, connect_by_isleaf isleaf from a_district start with name = '河南省' connect by prior id = parent_id order by id

上行递归查询 杞县 路径

select id, name, parent_id, substr(sys_connect_by_path(name,'->'),3) name_path from a_district 
       start with name='杞县' connect by prior parent_id = id order by id

下行递归查询 河南省 路径

select id, name, parent_id, substr(sys_connect_by_path(name,'->'),3) name_path from a_district 
       start with name='河南省' connect by prior id = parent_id order by id

综合使用

select id, name, parent_id, level, connect_by_isleaf isleaf, substr(sys_connect_by_path(name,'->'),3) name_path from a_district
        start with name='河南省' connect by prior id = parent_id order by id