转自:http://www.2cto.com/database/201108/101766.html
Oracle中的select语句可以用start with...connect by prior子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是:
select ... from <TableName> where <Conditional-1> start with <Conditional-2> connect by [prior] <Conditional-3>;
<Conditional-1>:过滤条件,用于对返回的所有记录进行过滤。
<Conditional-2>:查询结果重起始根结点的限定条件。
<Conditional-3>:连接条件
数据组织结构如下图:
------a-----a1
| |
| |----a2
|
|------b------b1
| |
| |------b2
数据库表结构如下:
create table t2( root_id number, id number, name varchar(5), description varchar2(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;
------a-----a1
| |
| |----a2
|
|------b------b1
| |
| |------b2
获取特定子树:
select * from t2 start with id = 1 connect by prior id = root_id;
------a-----a1
| |
| |----a2
|
select * from t2 start with id = 4 connect by prior id = root_id;
|------b------b1
| |
| |------b2
如果connect by prior中的prior被省略,则查询将不进行深层递归。
如:
select * from t2 start with root_id = 0 connect by id = root_id;
|------a
|
|------b
select * from t2 start with id = 1 connect by id = root_id;
如:
|------a
select t.*, level from t2 start with root_id =0 connect by id = prior root_id;
相关推荐
NULL 博文链接:https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
在 Oracle 中是通过 start with connect by prior 语法来实现递归查询的。 按照 prior 关键字在子节点端还是父节点端,以及是否包含当前查询的节点,共分为四种情况。 prior 在子节点端(向下递归) 第一种情况: ...
Oracle 查询树型关系是指使用 START WITH 和 CONNECT BY 子句来实现 SQL 的层次查询。从 Oracle 9i 开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 ...
oracle菜单树查询 使用实例 使用START WITH CONNECT BY PRIOR子句实现递归查询
oracle中的数查询,介绍的详细,有例子。
本文介绍Oracle中使用START WITH...CONNECT BY PRIOR子句实现递归查询树形结构的方法,小伙伴们可以参考一下。
Oracle row_number()over start with...connect by prior start with...connect by prior
Oracle使用递归查询。查询树结构的sql。在Oracle中,递归查询要用到start with ……connect by prior……
Oracle_start_with_connect_by_prior_用法[文].pdf
Oracle中的select语句可以用START WITH…CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是: select * from tablename start with cond1 connect by cond2 where cond3; ...
前言 对于数据库中的树形结构数据,如...oracle实现递归查询的话,就可以使用start with … connect by connect by递归查询基本语法是: select 1 from 表格 start with … connect by prior id = pId start with
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
Oracle RMAN 11g Backup And Recovery (中文名:Oracle RMAN 11g 备份和恢复) 英文原版图书,Oracle官方出版,学习Oracle Rman备份和恢复的最佳学习资料,与大家一起分享 Master Oracle Recovery MasterProtect your...
connect by prior数据库树的应用
Prior to working at Oracle, Kyte was a systems integrator who built large-scale, heterogeneous databases and applications for military and government clients. Tom Kyte is the same "Ask Tom" whose ...