Oracle Connect By用法

来源:互联网 发布:软件招标评分表 编辑:程序博客网 时间:2024/06/20 01:26

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