oracle connect by 递归,反递归,自动补全查询实现

时间:2023-03-09 16:43:15
oracle connect by 递归,反递归,自动补全查询实现

递归:

select *
    from t_pams_solution t
   start with t.id is null
  connect by prior id = t.parent_id
   order by level desc

红字部分分别为 表名,开始条件,子节点与根关联条件

反递归:

select rownum num,
         level lev,
         connect_by_root(id) id,
         connect_by_root(solution_name) name
    from t_pams_solution t
   where t.id = 23379
   start with 1 = 1
  connect by prior id = t.parent_id
   order by level desc

红字部分分别为 表名,开始条件,子节点与根关联条件

自动补全查询:

select level from dual
connect by level <10