oracle 查询树结构 select ... start with ...connect by piror

查询树结构

查询下级子节点

 select par_id,
        p_par_id,
        level,！--查询树节点层级
        CASE
          WHEN CONNECT_BY_ISLEAF = 0 THEN   !-- CONNECT_BY_ISLEAF  是伪列查询是否是叶子节点
           '枝'
          WHEN CONNECT_BY_ISLEAF = 1 THEN
           '叶子'
          ELSE
           '其他'
        END
   from par_real_info
  start with par_id = 'tj000001'         !-- start with后跟的是子节点则会查出根节点相关数据，如果是父节点则不会查出父节点相关数据
 connect by prior par_id = p_par_id !--prior 在子节点侧查询子节点

  order by level;

//多表链接查询

 select t.par_name,

        t.par_id,
        r.endda,
        level,
        CASE  CONNECT_BY_ISLEAF 
          WHEN  0 THEN
           '枝'
          WHEN  1 THEN
           '叶子'
          ELSE
           '其他'
        END
   from par_baseinfo t, par_real_info r
  start with t.par_id = 'tj000001'
         and t.in_use_or_revoke = '1'
         and r.endda = '99991231'
         and t.par_id = r.par_id
 connect by prior r.par_id = r.p_par_id
        and t.in_use_or_revoke = '1'
        and r.endda = '99991231'
        and t.par_id = r.par_id
  order by level;