Oracle start with...connect by prior 实现递归查询
浏览数：20 /
时间：2015年06月12日
基本语法：
<span style="font-size:18px;"><em>select ... from <TableName> where <Conditional-1> start with <Conditional-2> connect by <Conditional-3>; <Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。 <Conditional-2>：查询结果重起始根结点的限定条件。 <Conditional-3>：连接条件</em></span>
数据表结构如下：
create table table_test( priorId number, id number, name varchar(5), description varchar(10) );
测试数据：
insert into table_test(priorId,id,name,description) values(0,1,'f','f父类'); insert into table_test(priorId,id,name,description) values(1,2,'f1','f子类1'); insert into table_test(priorId,id,name,description) values(1,3,'f2','f子类2'); insert into table_test(priorId,id,name,description) values(0,4,'F','F父类'); insert into table_test(priorId,id,name,description) values(4,5,'F1','F子类1'); insert into table_test(priorId,id,name,description) values(4,6,'F2','F子类2');
获取完整树：
select * from table_test start with priorId = 0 connect by prior id = priorId;
获取特定子树：
select * from table_test start with id = 1 connect by prior id = priorId; select * from table_test start with id = 4 connect by prior id = priorId;
如果connect by prior中的prior被省略，则查询将不进行深层递归：
select * from table_test start with priorId = 0 connect by id = priorId; select * from table_test start with id = 1 connect by id = priorId;
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
