oracle中用START WITH...CONNECT BY PRIOR子句实现递归查询
表结构为 tab (id ,pid 父节点,name 名称)
数据如下:
Id | pid | Name |
1 | 0 | aa |
2 | 1 | bb |
3 | 1 | cc |
4 | 2 | dd |
5 | 2 | ee |
6 | 2 | ff |
7 | 3 | gg |
8 | 3 | hh |
9 | 3 | mm |
oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是:
select ... from tablename start with cond1
connect by cond2
where cond3;
通过子节点向根节点追朔.
select * from tab start with id=3 connect by prior pid=id;
通过根节点遍历子节点:
select * from persons.dept start with pid=0 connect by prior id=pid;