Oracle 递归查询connect by 简单例子

最近回顾了下connect 的使用方法，在此做个简单的总结，以供参考。

 

主要用到以下几点：
connect by :递归规则
start with :递归开始条件
prior :
正树:  必须是  ‘父’= prior ‘子’
倒树:  必须是  ‘子’= prior ‘父’
sys_connect_by_path(字段名, 2个字段之间的连接符号) ：用来显示当前成员相关的成员关系。

 

下面附上例子一枚：

WITH t AS
( 
   SELECT 1 AS emp_id,0 AS m_id   FROM dual UNION
   SELECT 2 AS emp_id,1 AS m_id   FROM dual UNION
   SELECT 3 AS emp_id,1 AS m_id   FROM dual UNION
   SELECT 4 AS emp_id,2 AS m_id   FROM dual UNION
   SELECT 5 AS emp_id,3 AS m_id   FROM dual
  
)
SELECT emp_id,m_id,
       sys_connect_by_path(emp_id, '~') full_parent_id
FROM t CONNECT BY PRIOR emp_id=m_id START WITH m_id=0;

 

结果：