oracle及其他数据库的递归查询语句

 

create table RS_DEPARTMENT_INFORMATION
(
  R_DEPARTMENT_ID        NUMBER(6) not null,
  R_DEPARTMENT_LV        VARCHAR2(20),
  R_FATHER_DEPARTEMNT_ID NUMBER(6) not null,
 )

oracle的递归查询语句：select t.r_department_id from rs_department_information t
    start with t.r_department_id = 1
    connect by prior t.r_department_id = t.r_father_departemnt_id

其他数据库递归查询语句：SELECT root.r_department_id, root.r_father_departemnt_id from rs_department_information root
            WHERE root.r_department_id=1
     UNION ALL
        SELECT sub.r_department_id, sub.r_father_departemnt_id
            FROM rs_department_information sub, rs_department_information super
          WHERE sub.r_father_departemnt_id= super.r_department_id