Oracle递归查询 oracle递归查询下级

Oracle递归查询在多层次的表中应用广泛，实用性比较强，话不多说，上代码！

有这样子的一个表：

1.简单递归查询（自上而下）

--level是系统自带的列，表示第几层，CONNECT_BY_ISLEAF 表示是否是叶子节点

select a.*,level,CONNECT_BY_ISLEAF from DG_TEST a start with value = '根节点' connect by prior id = pid;

2.简单递归查询（自下而上）

select a.* from DG_TEST a start with value = '三层子节点1' connect by prior pid = id;

运行结果如下：

3.查询三层节点的根节点

select id,pid,value,connect_by_root(id) as parentID,connect_by_root(value) from DG_TEST where value='三层子节点1' start with pid is null connect by prior id = pid;--根节点的pid为空

运行结果

4.递归路径

select a.*,substr(SYS_CONNECT_BY_PATH(a.value,'->'),3) as path from DG_TEST a start with value = '根节点' connect by prior id = pid;

运行结果