Oracle实现递归查询

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zhangzeyuaaa/article/details/52303698

有如下表Tree:

现在要递归查询冶炼(id=2)的所有父节点和子节点:

SELECT * FROM tree START WITH id = 2 CONNECT BY PRIOR pid = id -- 递归查询父节点
union
SELECT * FROM tree START WITH id = 2 CONNECT BY pid = PRIOR id; -- 递归查询子节点
查询结果:


当然,也可以使用in关键字递归查询多个节点的父节点和子节点:

SELECT * FROM tree START WITH id in (1, 10) CONNECT BY PRIOR pid = id -- 递归查询父节点
union
SELECT * FROM tree START WITH id in (1, 10) CONNECT BY pid = PRIOR id; -- 递归查询子节点
以上语句递归查询id为1或者10的父节点和子节点,结果如下:




阅读更多
换一批

没有更多推荐了,返回首页