现有如下表:test2
现在要递归查询WARD(id=2)的所有父节点和子节点:
select * from test2 start with id = 2 connect by prior pid = id --递归查询父节点
union
select * from test2 start with id = 2 connect by pid = prior id --递归查询子节点
查询结果:
当然,也可以使用in关键字递归查询多个节点的父节点和子节点:
select * from test2 start with id in (2,3) connect by prior pid = id --递归查询父节点
union
select * from test2 start with id in (2,3) connect by pid = prior id --递归查询子节点
查询结果:
补注:也可以加 where 和 and限制