前面提到oracle中start with ...connect by 除了用来进行层次查询和得到序列之外,还看到了在Oracle中利用深度优先法则进行connect by 运算。9I之后还增加了sys_connect_by_path 函数。说实话,我一直对这两个用法不得要领,只好期待哪天脑袋被门夹了,说不定能豁然开朗……….
玩笑归玩笑,动手实践才是最重要。
ORACLE 10G,PLSQL DEVELOP,创建测试表,插入测试数据
create table test(parent_id varchar2(20),lower_id varchar2(20));
insert into test values('0','1');
insert into test values('0','2');
insert into test values('1','11');
insert into test values('1','12');
insert into test values('2','21');
insert into test values('2','22');
insert into test values('11','111');
insert into test values('11','112');
insert into test values('12','121');
insert into test values('12','122');
insert into test values('21','211');
insert into test values('21','212');
insert into test values('22','221');
insert into test values('22','222');
commit;
CONNECT BY :
SQL> select test.*,level from test start with parent_id='0' connect by prior lower_id=parent_id;
PARENT_ID LOWER_ID LEVEL
-------------------- -------------------- ----------
0 1 1
1 11 2
11 111 3
11 112 3
1 12 2
12 121 3
12 122 3
0 2 1
2 21 2
21 211 3
21 212 3
2 22 2
22 221 3
22 222 3
14 rows selected.
SQL>
SQL> select test.* from test start with lower_id='222' connect by prior parent_id=lower_id;
PARENT_ID LOWER_ID
-------------------- --------------------
22 222
2 22
0 2
SQL>
从这两种写法可以看出,分别从根向叶子节点遍历,和从叶子节点向根遍历,得到树的结果。LEVEL是connect by 树中的伪列,标识这个叶子是在树当中的第几层。
SYS_CONNECT_BY_PATH
用法:SYS_CONNECT_BY_PATH(colum,char)
SQL> col aa format a10
SQL> select sys_connect_by_path(lower_id,' ') aa,level from test start with parent_id='0' connect by prior lower_id=parent_id;
AA LEVEL
---------- ----------
1 1
1 11 2
1 11 111 3
1 11 112 3
1 12 2
1 12 121 3
1 12 122 3
2 1
2 21 2
2 21 211 3
2 21 212 3
2 22 2
2 22 221 3
2 22 222 3
14 rows selected.
Elapsed: 00:00:00.01
SQL>
转摘自:http://www.cnblogs.com/zeromyth/archive/2009/09/08/1562346.html
分享到:
相关推荐
本文章详细介绍了Oracle中connect by...start with...的用法。
Oracle start with.connect by prior子句实现递归查询
NULL 博文链接:https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354
在Oracle中用Start with...Connect By子句递归查询
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
Successfully design and simulate your 3D robot model and use powerful robotics algorithms and tools to program and set up your robots with an unparalleled experience by using the exciting new features...
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
oracle中的数查询,介绍的详细,有例子。
Oracle row_number()over start with...connect by prior start with...connect by prior
Oracle_start_with_connect_by_prior_用法[文].pdf
In a world where understanding big data has become key, by mastering R you will be able to deal with your data effectively and efficiently. This book will give you the guidance you need to build and...
Source Code for Hello World with Pygame ................................................................................ 7 Setting Up a Pygame Program ....................................................
Oracle 递归查询, start with connect by prior 用法 find_in_set 函数 concat,concat_ws,group_concat 函数 MySQL 自定义函数 手动实现 MySQL 递归查询 Oracle 递归查询 在 Oracle 中是通过 start ...
A practical guide with clear instructions to design and develop a complete web application from start to finish Who This Book Is For This book is designed for JavaScript developers of any skill level ...
You'll start by creating, customizing, and extending HTTP-based web APIs and move on to host your web API with ASP.NET. Then, using a range of different features, you'll learn how to connect your ...
ORACLE查询树型关系(connect_by_prior_start_with)
Oracle 数据库树形结构用法总结,例如SYS_CONNECT_BY_PATH 、START WITH . . . CONNECT BY . . .等具体语法介绍
We start with the basics of networking and then explore how Java supports the development of client/server and peer-to-peer applications. The NIO packages are examined as well as multitasking and how ...
介绍了将多行转为字符串的三种方案,并比较了三种方案的执行效率. 1.sys_connect_by_path + start with ... connect by ... prior + 分析函数 2.自定义Function/SP 3.使用 Oracle 10g 内置函数 wmsys.wm_concat