oracle 递归查询

1. select  *  from t_table start  with id =0 connect   by   prior parentid=id

Sql代码

1. select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid   

 select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid 

 

通过根节点遍历子节点.

1. select  *  from t_table start  with id =0  connect   by   prior   parentid=id

Sql代码

1. select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid   

select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid 

 

可通过level 关键字查询所在层次.

1. select t.*, level   from t_table t start  with t.id=0  connect   by   prior t. parentid=t.id

Sql代码

1. select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid   

select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid 

 

start with 后面跟的是递归的种子。

connect by prior 它指明了查询的方向。

connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询；

1. select FIRST_VALUE(id) OVER(ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid
     from t_dbinfo_cata
    start with id = 4
   connect by prior parentid = id
2. 在start之前可以加入where子句进行条件查询
3. 在末尾可以加入排序字段