Oracle中start with...connect by子句实例

一.

select /*+ index(emp IDX_PAIC_EMP_INFO_004)*/ og.deptid_descr || '直属' "orgName",
       og.id_deptid "orgCode",
       emp.PAIC_UM_NUM "umId",
       emp.LAST_NAME "name",
       emp.paic_mobile_phone "telephone"
  from SIEBEL.ETL_PS_PAIC_ORG_CHG_ST og, siebel.etl_ps_paic_emp_info emp
 where og.tree_level_num = 2
   and trim(PAIC_DEPT_ENDDT) is null
   and og.id_deptid in ('S000031289', 'S000031290', 'S000031292')
   and og.id_deptid = emp.deptid
   and TRIM(emp.PAIC_LEAVE_DATE) IS NULL
   AND emp.EMPL_STATUS != 'T'
union all
select /*+ index(emp IDX_PAIC_EMP_INFO_004)*/ level3og."orgName",
       level3og."orgCode",
       emp.PAIC_UM_NUM       "umId",
       emp.LAST_NAME         "name",
       emp.paic_mobile_phone "telephone"
  from (select og.deptid_descr "orgName",
               og.id_deptid "orgCode",
               og.tree_level_num "orgLevel",
               sys_connect_by_path(og.id_deptid, '/') path
          from SIEBEL.ETL_PS_PAIC_ORG_CHG_ST og
         where trim(PAIC_DEPT_ENDDT) is null
         start with og.id_deptid = 'S000000006'
        connect by prior id_deptid = parent_node_name) level3og,
       (select level as "LEVEL",
               deptid_descr name,
               id_deptid,
               sys_connect_by_path(og.id_deptid, '/') path
          from SIEBEL.ETL_PS_PAIC_ORG_CHG_ST og
         where trim(PAIC_DEPT_ENDDT) is null
         start with og.id_deptid = 'S000000006'
        connect by prior id_deptid = parent_node_name) og,
       siebel.etl_ps_paic_emp_info emp
 where level3og."orgLevel" = 3
   and level3og."orgCode" != 'S000022969'
   and og.path like level3og.path || '%'
   and og.id_deptid = emp.deptid
   and TRIM(emp.PAIC_LEAVE_DATE) IS NULL
   AND emp.EMPL_STATUS != 'T'

二.

select "orgName", "orgCode", "orgLevel"
 from (select og.deptid_descr || '直属' "orgName",
              og.id_deptid "orgCode",
              og.tree_level_num "orgLevel",
              parent_node_name
         from SIEBEL.ETL_PS_PAIC_ORG_CHG_ST og
        where og.tree_level_num = 2
          and trim(PAIC_DEPT_ENDDT) is null
          and og.id_deptid in (SELECT V.VAL
                          FROM SIEBEL.S_LST_OF_VAL V
                         WHERE V.TYPE = 'ORG_WHITELIST')
       union all
       select og.deptid_descr   "orgName",
              og.id_deptid      "orgCode",
              og.tree_level_num "orgLevel",
              parent_node_name
         from SIEBEL.ETL_PS_PAIC_ORG_CHG_ST og
        where og.tree_level_num = 3
          and trim(PAIC_DEPT_ENDDT) is null   
          and og.id_deptid not in (SELECT V.VAL
                          FROM SIEBEL.S_LST_OF_VAL V
                         WHERE V.TYPE = 'ORG_BLACKLIST'))
order by decode("orgLevel", 2, "orgCode", parent_node_name), "orgCode"