语法格式:
select [level], column, expr... from table [where condition] start with condition connect by [prior column1= column2 |column1 = prior column2];
层次查询是通过start with和connect by子句标识的:
1.其中level关键字是可选的,表示等级,1表示root,2表示root的child,其他相同的规则。
2.From之后可以是table,view但是只能是一个table。
3.Where条件限制了查询返回的行,但是不影响层次关系,属于将节点截断,但是这个被截断的节点的下层child不受影响。
4.Start with是表示开始节点,对于一个真实的层次关系,必须要有这个子句,但是不是必须的。
5.connect by prior是指定父子关系,其中prior的位置不一定要在connect by之后,对于一个真实的层次关系,这也是必须的。
对于from是视图的,那么这个view不能包含join。
层次查询限制
1.层次查询from 之后如果是table,只能是一个table,不能有join。
2.from之后如果是view,则view不能是带join的。
3.使用order by子句,order子句是在等级层次做完之后开始的,所以对于层次查询来说没有什么意义,除非特别关注level,获得某行在层次中的深度,但是这两种都会破坏层次。见增强特性中的使用siblings排序。
4.在start with中表达式可以有子查询,但是connect by中不能有子查询。
层次查询的增强特性
1、SYS_CONNECT_BY_PATH
Oracle 9i提供了sys_connect_by_path(column,char),其中column是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。
下面的是oracle10g新增特性
2、 CONNECT_BY_ISLEAF
在oracle9i的时候,查找指定root下的叶子节点,是很复杂的,oracle10g引入了一个新的函数,connect_by_isleaf,如果行的值为0表示不是叶子节点,1表示是叶子节点。
3、CONNECT_BY_ISCYCLE和NOCYCLE关键字
如果从root节点开始找其子孙,找到一行,结果发生和祖先互为子孙的情况,则发生循环,oracle会报ORA-01436: CONNECT BY loop in user data,在9i中只能将发生死循环的不加入到树中或删除,在10g中可以用nocycle关键字加在connect by之后,避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环。0表示未发生循环,1表示发生了循环。
4、CONNECT_BY_ROOT
Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。
5、使用SIBLINGS关键字排序
对于层次查询如果用order by排序,比如order by last_name则是先做完层次获得level,然后按last_name排序,这样破坏了层次,比如特别关注某行的深度,按level排序,也是会破坏层次的。
在oracle10g中,增加了siblings关键字的排序。
语法:order siblings by <expre>
它会保护层次,并且在每个等级中按expre排序。
案例:
--测试表A B C D 4层数据 create table DATAS( ID NUMBER, DATES DATE, IDNEXT NUMBER ) --存在B、C情况 insert into datas values(1,to_date('2015-01-01','yyyy-mm-dd'),2); insert into datas values(2,to_date('2015-01-11','yyyy-mm-dd'),3); insert into datas values(3,to_date('2015-01-14','yyyy-mm-dd'),4); insert into datas values(4,to_date('2015-01-19','yyyy-mm-dd'),null); --不存在B、C情况 insert into datas values(11,to_date('2015-02-01','yyyy-mm-dd'),14); insert into datas values(14,to_date('2015-02-11','yyyy-mm-dd'),null); --查询语句 SELECT ID,DATES, CONNECT_BY_ROOT ID "ROOT", CONNECT_BY_ISLEAF "ISLEAF", LEVEL, SYS_CONNECT_BY_PATH(ID, '-') "PATH" FROM DATAS START WITH IDNEXT IS NULL CONNECT BY PRIOR ID = IDNEXT ORDER SIBLINGS BY ID;
ID DATES ROOT ISLEAF LEVEL PATH
------- ----------- ---------- ---------- ---------- -----------
4 1/19/2015 4 0 1 -4
3 1/14/2015 4 0 2 -4-3
2 1/11/2015 4 0 3 -4-3-2
1 1/1/2015 4 1 4 -4-3-2-1
14 2/11/2015 14 0 1 -14
11 2/1/2015 14 1 2 -14-11
相关推荐
Connect By 可以列出上下级关系 构造序列 求排列组合 逆转求出下上级的关系路径
在Oracle中用Start with...Connect By子句递归查询
NULL 博文链接:https://freejvm.iteye.com/blog/550858
本文章详细介绍了Oracle中connect by...start with...的用法。
oracle connect by 和 分析函数总结.doc
通过实例比较了 SYS_CONNECT_BY_PATH 和 CONNECT_BY_ROOT 的异同,和返回树形的数据结构
Oracle Connect by
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
Oracle start with.connect by prior子句实现递归查询
oracle中的数查询,介绍的详细,有例子。
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
ORACLE查询树型关系(connect_by_prior_start_with)
文档详述问题的解决办法,附资源instantclient_11_2.zip,一键解决问题
韩顺平oracle学习笔记 第0讲:如何学习oracle 一、如何学习oracle Oracle目前最流行的数据库之一,功能强大,性能卓越。学习oracle需要具备一定基础: 1.学习过一门编程语言(如:java ,c) 2.最好学习过一门别的...
oracle跨库查询 --语法 /*定义 create [public] databse link _link_name connect to _username identified by _passowrd using '_servername/_serverurl'; */ /*调用 select * from _table_name@_link_name */...
行列转换,层级关系,oracle sys_connect_by_path的用法
Oracle_start_with_connect_by_prior_用法[文].pdf
NULL 博文链接:https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354
许多情况下,由于程序中需要将行转为列展示,如果使用ORACLE那么这个资源适合你。