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

最新推荐文章于 2025-08-31 12:41:23 发布

最新推荐文章于 2025-08-31 12:41:23 发布 · 160 阅读

文章标签：

#Oracle

本文介绍如何使用Oracle及通用SQL进行递归查询来获取部门层级信息。针对Oracle数据库，利用CONNECT BY PRIOR子句实现递归；对于其他数据库，则采用UNION ALL结合自连接的方式实现相似功能。

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