oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是:
select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>;
;
Conditional-1:过滤条件 Conditional-2:启始条件 Conditional-3:连接条件
数据库表结构:
create table t(
rootId number,
id number,
name varchar2(5),
descId varchar2(10)
);
insert into t values('0','1','a','aaaa1')
insert into t values('1','2','a1','aaaa2')
insert into t values('1','3','a2','aaaa3')
insert into t values('0','4','b','bbbb1')
insert into t values('4','5','b1','bbbb2')
insert into t values('4','6','b2','bbbb3')
简单例子:
获取完整树select * from t with rootId=0 connect by prior id=rootId
|--a ____a1
| |____a2
|
|--b ____b1
| |____b2
with id=1,或者id=4来获取特定子树
|--a ____a1
| |____a2 或者 .....
如果prior关键字被省略,则不进行深层递归
select * from t with rootId=0 connect by id=rootId
|————a
|————b