CONNECT BY用法总结

 

• 测试数据: DM_FPKF   包括三个字段FPKF_DM（库房代码）、SJ_FPKF_DM（上级库房代码）、ZKFBZ（主库房标志）

  图片为测试结果结果

   

  CONNECT BY 的用法：

   

  --以23415000000000为根节点，深度优先遍历

  select level,a.* from fpglhx.dm_fpkf a

  start with a.fpkf_dm = '234150000000000'

  connect by prior a.fpkf_dm = a.sj_fpkf_dm;

   

  
  

  --以23415000000000为根节点，深度优先遍历（不包括根节点）

  select level,a.* from fpglhx.dm_fpkf a

  start with a.sj_fpkf_dm = '234150000000000'

  connect by prior a.fpkf_dm = a.sj_fpkf_dm;

   

  
  

  --以23415000000000为子节点，逐级向上寻找遍历父节点

  select level,a.* from fpglhx.dm_fpkf a

  start with a.fpkf_dm = '234150000000000'

  connect by prior a.sj_fpkf_dm = a.fpkf_dm;

  
  

   

  --以23415000000000为父节点，逐级向上遍历

  select level,a.* from fpglhx.dm_fpkf a

  start with a.sj_fpkf_dm = '234150000000000'

  connect by prior a.sj_fpkf_dm = a.fpkf_dm;

  
  

   

   

  --以23415000000000为根节点，广度优先遍历

  select level,a.fpkf_dm,a.sj_fpkf_dm,a.zkfbz from fpglhx.dm_fpkf a

  start with a.fpkf_dm = '234150000000000'

  connect by prior a.fpkf_dm = a.sj_fpkf_dm

  order by level;

  
  

   

  --寻找叶子节点

  SELECT

         connect_by_isleaf  "是否是叶子节点",

         a.fpkf_dm,a.sj_fpkf_dm,a.zkfbz

    FROM fpglhx.dm_fpkf a

  start with a.fpkf_dm = '234150000000000'

  connect by prior a.fpkf_dm = a.sj_fpkf_dm;

  
  

   

  --遍历路径，根节点

  SELECT LEVEL  "等级",

         CONNECT_BY_ISLEAF "是否是叶子节点",

         CONNECT_BY_ROOT a.fpkf_dm ENAME,

         LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(a.fpkf_dm, '=>'),

         a.*

    FROM fpglhx.dm_fpkf a

  connect by prior a.fpkf_dm = a.sj_fpkf_dm;

  
  

   

   

  节点234150000000000到节点234152118000002的路径

  SELECT

         SYS_CONNECT_BY_PATH(a.fpkf_dm,'=>')

    FROM fpglhx.dm_fpkf a

    where a.fpkf_dm = '234152118000002'

  connect by prior a.fpkf_dm = a.sj_fpkf_dm

  start with a.fpkf_dm = '234150000000000';