在SELECT命令中使用CONNECT BY 和START WITH 子句可以查询表中的树形结构关系。其命令格式如下:
SELECT ….. CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
[START WITH];
其中:CONNECT BY子句说明每行资料将是按层次顺序检索,并规定将表中的资料连入树形结构的关系中。
0PRIORY运算符必须放置在连接关系的两列中的某一个的前面。对于节点间的父子关系,PRIOR去处符在一侧表示父节点,
在别一侧表示子节点,从而确定查找权结构的顺序是自顶向下还是自底向上。在连接关系中,除了可以使用列名外,
还允许使用列运算式。START WITH 子句为可选项,用来标识哪个节点作为查找树型结构的根节点。
例子:
create table test2(empno number,ename varchar2(30),managerid number);
insert into test2 values(10000,'martin',0);
insert into test2 values(10001,'binn',10000);
insert into test2 values(10002,'davit',10001);
insert into test2 values(10003,'kyte',10002);
insert into test2 values(10004,'shre',10003);
insert into test2 values(10006,'ivan',10002);
insert into test2 values(10007,'richard',10003);
select empno,ename,managerid,level,sys_connect_by_path(empno||ename,'<-') router
from test2
start with ename='martin'
--connect by empno=prior managerid
connect by prior empno=managerid
order by level desc;
select empno,ename,managerid,level,sys_connect_by_path(empno||ename,'->') router
from test2
start with ename='richard'
connect by empno=prior managerid
order by level desc;
总结:
所以,上述规则可以描述为:
01) 当操作符prior在parent前面时,得到child =>parent关系树
当prior在child前面时,得到parent =>child关系树
02) 当指定了start with时,过滤掉没有包含指定字段的所有关系;否则,不作任何过滤,显示所有关系
最简单实际使用小例子:
在有些项目中,下拉中用数据源取年份时,可用如下方式写:
select * from
(select rownum,to_char(add_months(sysdate, 4), 'yyyy') - rownum
from dual connect by rownum<5);
注意:用此函数多表关联出现问题与ORACLE版本有关,例子如下:
create or replace view VIEW_T1 as
/*
* 816不支持这种写法,9201下这种写法,在以Fld3为条件做查询时会导致记录数不正确
SELECT *
FROM T1 A, TCODE B
WHERE A.Fld1 = B.Fld1(+)
AND A.Fld3 = B.C_DM(+)
START WITH Fld2 IS NULL
CONNECT BY PRIOR Fld2_Farther= Fld2
*/
SELECT A.*, nvl(B.C_MC, A.Fld3) Fld3_MC from
(
SELECT *
FROM T1 A
START WITH Fld2 IS NULL
CONNECT BY PRIOR Fld2_Farther= Fld2
) A, TCODE B
WHERE A.Fld1 = B.Fld1(+)
AND A.Fld3 = B.C_DM(+)
=================================================================================
有关层次查询之前的文章参考如下。
【层次查询】Hierarchical Queries之LEVEL应用
http://space.itpub.net/519536/viewspace-623916
【层次查询】Hierarchical Queries之“树的遍历”
http://space.itpub.net/519536/viewspace-623809
【层次查询】Hierarchical Queries之CONNECT_BY_ISCYCLE伪列
http://space.itpub.net/519536/viewspace-624032
继LEVEL和CONNECT_BY_ISCYCLE伪列之后,最后一个可用的伪列名字叫做CONNECT_BY_ISLEAF,正如这个伪列名字中描述的那样,他可以指示出哪些是“叶子节点”,就是这么简单。
如果发现是叶子节点,该伪列会返回“1”,反之返回记录“0”。
1.回望那棵关系“树”,很直观,F、G和E节点是叶子节点。
A
/ \
B C
/ /
D E
/ \
F G
2.重温一下阐述上图的T表数据
sec@ora10g> select * from t;
X Y Z
---------- ---------- ----------
A 1
B 2 1
C 3 1
D 4 2
E 5 3
F 6 4
G 7 4
7 rows selected.
3.结合LEVEL伪列看一下CONNECT_BY_ISLEAF的直观效果
sec@ora10g> col tree for a16
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF from t start with x = 'A' connect by NOCYCLE prior y=z;
TREE CONNECT_BY_ISLEAF
---------------- -----------------
A 0
B 0
D 0
F 1
G 1
C 0
E 1
7 rows selected.
是不是有一种豁然开朗的感觉,对头,CONNECT_BY_ISLEAF功能就是这么简单。
4.如何仅筛选出上面结果中所有叶子节点?
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF from t where CONNECT_BY_ISLEAF=1 start with x = 'A' connect by NOCYCLE prior y=z;
TREE CONNECT_BY_ISLEAF
---------------- -----------------
F 1
G 1
E 1
5.得到每一层级叶子节点
可以理解下面的SQL是对“树”的逐层消除的过程。
1)不做消除,因为我们的树只有4层。
(1)SQL
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF, LEVEL from t where CONNECT_BY_ISLEAF=1 start with x = 'A' connect by NOCYCLE prior y=z and level<=4;
TREE CONNECT_BY_ISLEAF LEVEL
---------------- ----------------- ----------
F 1 4
G 1 4
E 1 3
(2)对应的“树”
A
/ \
B C
/ /
D E
/ \
F G
2)消除第四层
(1)SQL
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF, LEVEL from t where CONNECT_BY_ISLEAF=1 start with x = 'A' connect by NOCYCLE prior y=z and level<=3;
TREE CONNECT_BY_ISLEAF LEVEL
---------------- ----------------- ----------
D 1 3
E 1 3
(2)对应的“树”
A
/ \
B C
/ /
D E
3)消除第三层
(1)SQL
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF, LEVEL from t where CONNECT_BY_ISLEAF=1 start with x = 'A' connect by NOCYCLE prior y=z and level<=2;
TREE CONNECT_BY_ISLEAF LEVEL
---------------- ----------------- ----------
B 1 2
C 1 2
(2)对应的“树”
A
/ \
B C
4)消除第二层
(1)SQL
sec@ora10g> select lpad(' ',2*(level-1))||x tree, CONNECT_BY_ISLEAF, LEVEL from t where CONNECT_BY_ISLEAF=1 start with x = 'A' connect by NOCYCLE prior y=z and level<=1;
TREE CONNECT_BY_ISLEAF LEVEL
---------------- ----------------- ----------
A 1 1
(2)对应的“树”,此时只剩根节点了。
A
6.小结
有关层次查询中可用的伪列有LEVEL、CONNECT_BY_ISCYCLE还是本文所述的CONNECT_BY_ISLEAF伪列,这些伪列都是从可用性角度提出来的。稍作组合便可以得到意想不到的效果。更多有趣的细节请大家慢慢体会。
分享到:
相关推荐
Oracle start with.connect by prior子句实现递归查询
本文章详细介绍了Oracle中connect by...start with...的用法。
在Oracle中用Start with...Connect By子句递归查询
oracle中的数查询,介绍的详细,有例子。
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
NULL 博文链接:https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354
ORACLE查询树型关系(connect_by_prior_start_with)
Oracle 递归查询, start with connect by prior 用法 find_in_set 函数 concat,concat_ws,group_concat 函数 MySQL 自定义函数 手动实现 MySQL 递归查询 Oracle 递归查询 在 Oracle 中是通过 start ...
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
Oracle_start_with_connect_by_prior_用法[文].pdf
Oracle row_number()over start with...connect by prior start with...connect by prior
Oracle使用递归查询。查询树结构的sql。在Oracle中,递归查询要用到start with ……connect by prior……
Oracle 数据库树形结构用法总结,例如SYS_CONNECT_BY_PATH 、START WITH . . . CONNECT BY . . .等具体语法介绍
oracle菜单树查询 使用实例 使用START WITH CONNECT BY PRIOR子句实现递归查询
Oracle SELECT语句中的START WITH和CONNECT BY子句自动阻断一个层次。缺少这项特性,就需要一个复杂的自联接来确定行之间的逻辑联系。START WITH子句指定被认为是层次起点,或“根”的一行或几行,然后CONNECT BY ...
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat
详细介绍了,oracle的树状结构查询的语法start with… CONNECT BY…
一、ORACLE的启动和关闭 1、在单机环境下 要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 su - oracle a、启动ORACLE系统 oracle>svrmgrl SVRMGR>connect internal SVRMGR>startup SVRMGR>quit b、关闭...
如果设置为 N 表示不通过 dbstart 和 dbshut 启动和关闭实例数据库 CAMS 系统要求在 安装完 ORACLE 后要求将该参数修改为 Y 以保证 ORACLE 数据库自启动 和关闭 1.2.3 数据库实例初始化...