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 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');
---------------------------------------------------------------------------------------------------
--------------------------------------准备好了,开始吧-----------------------------------------
---------------------------------------------------------------------------------------------------
1. 获取完整树:
select * from t2 start with root_id = 0 connect by prior id = root_id;
结构图:
|---------a------a1
| |
| |
| |------a2
|
|---------b------b1
| |
| |
| |------b2
2. 获取特定子树:
2.1 select * from t2 start with id = 1 connect by prior id = root_id;
结构图:
|---------a------a1
| |
| |
| |------a2
2.2 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
以上内容从其他网友那里copy加工而成,特别感谢实例的原创者!!!
分享到:
相关推荐
Oracle start with.connect by prior子句实现递归查询
NULL 博文链接:https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
oracle中的数查询,介绍的详细,有例子。
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
Oracle_start_with_connect_by_prior_用法[文].pdf
Oracle row_number()over start with...connect by prior start with...connect by prior
ORACLE查询树型关系(connect_by_prior_start_with)
Oracle RMAN 11g Backup And Recovery (中文名:Oracle RMAN 11g 备份和恢复) 英文原版图书,Oracle官方出版,学习Oracle Rman备份和恢复的最佳学习资料,与大家一起分享 Master Oracle Recovery MasterProtect your...
Oracle 递归查询, start with connect by prior 用法 find_in_set 函数 concat,concat_ws,group_concat 函数 MySQL 自定义函数 手动实现 MySQL 递归查询 Oracle 递归查询 在 Oracle 中是通过 start ...
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
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 ...
Oracle Database 11g is a significant upgrade from prior releases of Oracle. New features give developers, database administrators, and end users greater control over the storage, processing, and ...
Data Mining with R: Learning with Case Studies, Second Edition uses practical examples to illustrate the power of R and data mining. Providing an extensive update to the best-selling first edition, ...
[Excel.2007.Power.Programming.with.VBA].Excel.2007.Power.Programming.with.VBA.part1 CHM格式书籍 Excel 2007 Power Programming with VBA John Walkenbach Wiley Publishing, Inc. Published by Wiley ...
一、 ORA-01436报错 开发遇到一个报错 ORA-01436: CONNECT BY loop in user data (ORA-01436: 用户数据中的 CONNECT BY 循环)。...CONNECT BY PRIOR r1.region_id = r1.parent_id; 如果数据在region
。。。
。。。