start with .. connect by prior ..

简介:

oracle中递归查询用start with ... connect by prior... 字句


语法:

select 字段 from 表名 where 条件 start with 条件 connect by 条件


假设有一张表结构如下:

create table test(id number, name varchar2(40), p_id number);


--查询id为10的所有子节点

--我的理解:先找到id=10的记录,然后找谁的p_id=10,如此找下去,是由根节点找叶节点

select * from test start with id = 10 connect by prior id = p_id;

<==>

select * from test start with id = 10 connect by p_id = prior id;


--查询id为10的所有父节点

--我的理解:先找到id=10的记录,然后找这条记录的p_id,再找p_id的p_id,如此找下去,直到找到根节点,是由叶节点找根节点

select * from test start with id = 10 connect by prior p_id = id;

<==>

select * from test start with id = 10 connect by id = prior p_id;


说明:

   1.prior如果省略,不进行深层递归查询

   2.prior如果在等号左边,表示从根节点向叶子节点检索,也叫做向下查询

   3.prior如果在等号右边,表示从叶子节点向根节点检索,也叫做向上查询


-------------------------------------------------------------------------------------------

补充:

    prior可以在操作符的任意一边,它的作用就是指定父节点。

wKioL1TNhCfwRr2SAALwQRFw2a4215.jpg

wKiom1TNgwrA5TWeAAHwBwcmd2k795.jpg


本文转自    手不要乱摸      51CTO博客,原文链接:http://blog.51cto.com/5880861/1397468


相关文章
|
2月前
|
负载均衡 Java 应用服务中间件
Client not connected, current status:STARTING
Client not connected, current status:STARTING
75 1
|
9月前
|
算法 Java
22-大厂面试题:Con-current Mode Failure如何导致以及解决
上文我们已经介绍了CMS垃圾收集器的工作原理以及流程,本篇我们接着深入说明CMS垃圾收集器的缺点以及所导致的一些问题应该如何解决。
93 0
|
10月前
|
数据库
Could not create connection to database server. Attempted reconnect 3 times. Giving up.
Could not create connection to database server. Attempted reconnect 3 times. Giving up.
112 0
|
11月前
【WARNING: Retrying (Retry(total=4, connect=None, read=None, redirect=None, st】
【WARNING: Retrying (Retry(total=4, connect=None, read=None, redirect=None, st】
143 0
执行HQL直接被退出:Remote side unexpectedly closed network connection
执行HQL直接被退出:Remote side unexpectedly closed network connection
1041 0
执行HQL直接被退出:Remote side unexpectedly closed network connection
飞思卡尔codewarrior下载出现USBD connection error:Failed to connect to targe(Reason :Target reset pin time out)
飞思卡尔codewarrior下载出现USBD connection error:Failed to connect to targe(Reason :Target reset pin time out)
飞思卡尔codewarrior下载出现USBD connection error:Failed to connect to targe(Reason :Target reset pin time out)
|
缓存 网络协议 关系型数据库
从Host blocked报错学习max_connect_errors
1.案发现场-Host blocked 一个案例场景是大数据抽取job任务连接MySQL实例抽数,任务报错如下图所示:报错表示,host被锁是由于有大量的连接错误,如果要解锁就执行mysqladmin flush-hosts;知识点!!!线索:host被锁,大量连接错误证人提示线索:确认最近新加p.
1216 0
从Host blocked报错学习max_connect_errors