Oracle Connect By Prior用法

  1. 云栖社区>
  2. 博客>
  3. 正文

Oracle Connect By Prior用法

长征2号 2017-10-16 13:08:00 浏览585
展开阅读全文

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>:连接条件

  数据组织结构如下图:


a class=


数据库表结构如下:

  create table t2(

  root_id number,

  id number,

  name varchar(5),

  description varchar(10)

  );

  insert into t2(root_id,id,name,description) values(0,1,'a','aaa');

  insert into t2(root_id,id,name,description) values(1,2,'a1','aaa1');

  insert into t2(root_id,id,name,description) values(1,3,'a2','aaa2');

  insert into t2(root_id,id,name,description) values(0,4,'b','bbb');

  insert into t2(root_id,id,name,description) values(4,5,'b1','bbb1');

  insert into t2(root_id,id,name,description) values(4,6,'b2','bbb2');

  获取完整树:

  select * from t2 start with root_id = 0 connect by prior id = root_id;

Oracle Connect By Prior用法 - ekong - 仰天一笑泪光寒


    获取特定子树:select * from t2 start with id = 1 connect by prior id = root_id;

Oracle Connect By Prior用法 - ekong - 仰天一笑泪光寒


    select * from t2 start with id = 4 connect by prior id = root_id;

Oracle Connect By Prior用法 - ekong - 仰天一笑泪光寒



    如果connect by prior中的prior被省略,则查询将不进行深层递归。如:

    select * from t2 start with root_id = 0 connect by id = root_id;

Oracle Connect By Prior用法 - ekong - 仰天一笑泪光寒

    select * from t2 start with id = 1 connect by id = root_id;如:
Oracle Connect By Prior用法 - ekong - 仰天一笑泪光寒


本文转自einyboy博客园博客,原文链接:http://www.cnblogs.com/einyboy/archive/2012/08/01/2617939.html,如需转载请自行联系原作者。


网友评论

登录后评论
0/500
评论
长征2号
+ 关注