Oracle 两表连接的递归查询

Oracle 的递归查询：

      1.  从上级递归查下级        

select  * from tbl_test  start  with  id  = ? connect  by  prior  id  = pid

     2.从下级递归查上级

select  * from  tbl_test  start  with id= ? connect  by  prior pid = id

    3.  两表连接递归查询

        在这里假设有两张表，部门表（tbl_department） 和员工表（tbl_employee）

       根据指定的部门ID，查询所有该部门的员工 及 子部门的员工

 

select emp.*,dept.name dept_name from tbl_employee emp, tbl_department dept
 where emp.dept_id = dept.id and 
( emp.dept_id = ? or emp.dept_id in 
( select a.id from tbl_department a 
start with a.id = ? connect by prior a.id = a.upper_id ) ) 
order by emp.id