oracle递归笔记

数据表：

1.查询节点下的所有子节点

例：查询广州市这个节点下的所有子节点。

SELECT *
  FROM CITY
 START WITH NAME = '广州市'
CONNECT BY PRIOR ID = PARENT_ID

2.查询节点的所有根节点

例：查询广州市节点的所有根节点

SELECT *
  FROM CITY
 START WITH NAME = '广州市'
CONNECT BY PRIOR PARENT_ID = id

3.利用in查询多个节点的子节点

例：查询广州市和惠州市的节点下的子节点。

SELECT *
  FROM CITY
 START WITH NAME in ('广州市' , '惠州市')
CONNECT BY PRIOR ID = PARENT_ID

注：同样的查询多个节点的父节点一样的方法，参考1和3的区别，就知道该怎么写啦。

4.查询一个节点的兄弟节点

例：查询海珠区的兄弟节点，其实查询兄弟节点就是查询相同的parent_id的节点

select *
  from CITY t
 where exists (select *
          from CITY t2
         where t.parent_id = t2.parent_id
           and t2.name = '海珠区')

或则也可以写成下面那这种

select *
  from city t
 where t.parent_id in
       (select t2.parent_id from city t2 where t2.name = '海珠区')

显示结果都是一样的

5.查询一个节点的同级节点

那么如果我要查询的不只是一个节点的兄弟节点，还要包括他的族兄弟节点呢？

比如这里要查出和海珠区一个级别的行政区，就是查询结果还要包括其他市下面的区怎么实现？

如果在表中设置了级别的字段那么这个就非常简单，只要查询和海珠区同一个级别的即可，这里列出不使用该字段的实现方法。

with tmp as
 (select a.*, level leaf
    from city a
   start with a.parent_id is null
  connect by a.parent_id = prior a.id)
select * from tmp where leaf = (select leaf from tmp where name = '海珠区')

这里使用两个技巧，一个是使用了level来标识每个节点在表中的级别，还有就是使用with语法模拟出了一张带有级别的临时表。

6.查询父节点的兄弟节点（伯父和叔父）

查询父节点的兄弟节点其实可参考第4条，只需要将第四条添加一个子查询即可实现。

例：查询海珠区的父节点的兄弟节点

select *  
  from CITY t  
 where exists (select *  
          from CITY t2  
         where t.parent_id = t2.parent_id  
           and t2.id in (select parent_id from city where name = '海珠区'))

或

select *
  from city t
 where t.parent_id in
       (select t2.parent_id
          from city t2
         where t2.id in (select parent_id from city where name = '海珠区'))

7.查询父节点的同级节点

通过上一个可以知道其实这个和第5个是差不多的，相比于第五就是将最后的级别减1，即可。

with tmp as
 (select a.*, level leaf
    from city a
   start with a.parent_id is null
  connect by a.parent_id = prior a.id)
select * from tmp where leaf = (select leaf from tmp where name='海珠区') - 1

8.列出节点的路径

例：列出海珠区的节点路径

select sys_connect_by_path(name, '/')
  from city
 where name='海珠区'
 start with id=1
connect by parent_id = prior id

这就实现了将节点的路径输出，如果有人觉得这个路径最前面多了的一个斜杠需要去掉，可以使用ltrim（value,charlist）函数

select ltrim(sys_connect_by_path(name, '/'),'/')
  from city
 where name='海珠区'
 start with id=1
connect by parent_id = prior id

这里顺便贴出一个从节点到根节点的路径

select sys_connect_by_path (name, '/')
from city
start with name='海珠区'
connect by prior parent_id = id

9.判断当前节点下是否还有节点

如果有节点显示0，没有节点显示1

select  t.*,connect_by_isleaf
from city t
start with t.parent_id is null
connect by t.parent_id = prior t.id

start with表示开始节点，比如讲start with改成 t.name=‘广东省’，就显示广东省下的节点，由于上面是t.parent_id  is  null 所以显示的是所有节点

（这个自己动手写一写，对比一下就秒懂了）。

10.显示某个节点下的没有子节点的节点

显示某个节点下的没有子节点的节点，这个其实就是显示9中的connect_by_isleaf=1的内容（这个感觉还是很有用的）

select  t.*
from city t
where connect_by_isleaf=1
start with t.name='广东省'
connect by t.parent_id = prior t.id

上述自行总结贴出，如有不足或错误请指正，谢谢！