心似海
- 浏览: 459150 次
-
文章分类
最新评论
-
datawarehouse：
来学习了。
什么是informatic? -
nange223：
感谢分享，学习了
一些数据库监控,优化,管理工具 -
pianxibin：
ertrth thr dj dyj
一些数据库监控,优化,管理工具 -
gekky6：
多谢分享，学习下
一些数据库监控,优化,管理工具 -
lqlein：
好好学习学习
一些数据库监控,优化,管理工具
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表：
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
插入测试数据：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
start with id=1
connect by prior id = pid
从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id
start with id=5
connect by prior pid = id
=====
对于oracle进行简单树查询(递归查询)
| DEPTID | PAREDEPTID | NAME |
| NUMBER | NUMBER | CHAR (40 Byte) |
| 部门id | 父部门id(所属部门id) | 部门名称 |
通过子节点向根节点追朔.
Sql代码 
- select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
通过根节点遍历子节点.
Sql代码
- select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
可通过level 关键字查询所在层次.
Sql代码
- select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
再次复习一下:start with ...connect by 的用法， start with 后面所跟的就是就是递归的种子。
递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询；
connect by prior 后面所放的字段是有关系的，它指明了查询的方向。
练习： 通过子节点获得顶节点
Sql代码
- select FIRST_VALUE(deptid) OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid
====这种方法只是当表里就有一颗树,多棵树怎么办？
分享到：
评论
4 楼
fengzhongtian
2009-06-17
<div class="quote_title">心似海 写道</div>
<div class="quote_div">
<div>Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。</div>
<div>创建示例表：</div>
<div>CREATE TABLE TBL_TEST<br>(<br> ID NUMBER,<br> NAME VARCHAR2(100 BYTE),<br> PID NUMBER DEFAULT 0<br>);</div>
<div> </div>
<div>插入测试数据：</div>
<div>INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');<br>INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');<br>INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');<br>INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');<br>INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');</div>
<div> </div>
<div>从Root往树末梢递归</div>
<div>select * from TBL_TEST<br> start with id=1<br> connect by prior id = pid</div>
<div> </div>
<div>
<div>从末梢往树ROOT递归</div>
<div>select * from TBL_TEST<br> start with id=5<br> connect by prior pid = id</div>
<div>=====</div>
<div>
<h1>对于oracle进行简单树查询(递归查询)</h1>
<p> </p>
<table border="0"><tbody>
<tr>
<td>DEPTID</td>
<td>PAREDEPTID</td>
<td>NAME</td>
</tr>
<tr>
<td>NUMBER</td>
<td>NUMBER</td>
<td>CHAR (40 Byte)</td>
</tr>
<tr>
<td>部门id</td>
<td>父部门id(所属部门id)</td>
<td>部门名称</td>
</tr>
</tbody></table>
<p> </p>
<p>通过子节点向根节点追朔.</p>
<div class="dp-highlighter">
<div class="bar">
<div class="tools">Sql代码 <a title="复制代码" href="/topic/191016"><img src="/images/icon_copy.gif" alt="复制代码"></a>
</div>
</div>
<ol class="dp-sql">
<li><span><span class="keyword"><strong><span style="color: #7f0055;">select</span></strong></span><span> * </span><span class="keyword"><strong><span style="color: #7f0055;">from</span></strong></span><span> persons.dept start </span><span class="keyword"><strong><span style="color: #7f0055;">with</span></strong></span><span> deptid=76 </span><span class="keyword"><strong><span style="color: #7f0055;">connect</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">by</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">prior</span></strong></span><span> paredeptid=deptid </span></span></li>
</ol>
</div>
<pre name="code" class="sql"> select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid </pre>
<p> </p>
<p>通过根节点遍历子节点. </p>
<div class="dp-highlighter">
<div class="bar">
<div class="tools">Sql代码 <a title="复制代码" href="/topic/191016"><img src="/images/icon_copy.gif" alt="复制代码"></a>
</div>
</div>
<ol class="dp-sql">
<li><span><span class="keyword"><strong><span style="color: #7f0055;">select</span></strong></span><span> * </span><span class="keyword"><strong><span style="color: #7f0055;">from</span></strong></span><span> persons.dept start </span><span class="keyword"><strong><span style="color: #7f0055;">with</span></strong></span><span> paredeptid=0 </span><span class="keyword"><strong><span style="color: #7f0055;">connect</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">by</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">prior</span></strong></span><span> deptid=paredeptid </span></span></li>
</ol>
</div>
<pre name="code" class="sql">select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid </pre>
<p> </p>
<p>可通过level 关键字查询所在层次. </p>
<div class="dp-highlighter">
<div class="bar">
<div class="tools">Sql代码 </div>
</div>
<ol class="dp-sql">
<li><span><span class="keyword"><strong><span style="color: #7f0055;">select</span></strong></span><span> a.*,</span><span class="keyword"><strong><span style="color: #7f0055;">level</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">from</span></strong></span><span> persons.dept a start </span><span class="keyword"><strong><span style="color: #7f0055;">with</span></strong></span><span> paredeptid=0 </span><span class="keyword"><strong><span style="color: #7f0055;">connect</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">by</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">prior</span></strong></span><span> deptid=paredeptid </span></span></li>
</ol>
</div>
<pre name="code" class="sql">select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid </pre>
<p> </p>
<p>再次复习一下:start with ...connect by 的用法，<strong> <em><span style="text-decoration: underline;">start with</span></em></strong> 后面所跟的就是就是<span style="color: #ff0000;"><strong>递归的种子</strong></span>。 </p>
<p><span style="color: #ff0000;">递归的种子也就是递归开始的地方</span> connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询； </p>
<p>connect by prior 后面所放的字段是有关系的，<strong><span style="text-decoration: underline;">它指明了查询的方向</span></strong>。 </p>
<p>练习： 通过子节点获得顶节点 </p>
<div class="dp-highlighter">
<div class="bar">
<div class="tools">Sql代码 <a title="复制代码" href="/topic/191016"><img src="/images/icon_copy.gif" alt="复制代码"></a>
</div>
</div>
<ol class="dp-sql">
<li><span><span class="keyword"><strong><span style="color: #7f0055;">select</span></strong></span><span> FIRST_VALUE(deptid) OVER (</span><span class="keyword"><strong><span style="color: #7f0055;">ORDER</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">BY</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">LEVEL</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">DESC</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">ROWS</span></strong></span><span> UNBOUNDED PRECEDING) </span><span class="keyword"><strong><span style="color: #7f0055;">AS</span></strong></span><span> firstdeptid </span><span class="keyword"><strong><span style="color: #7f0055;">from</span></strong></span><span> persons.dept start </span><span class="keyword"><strong><span style="color: #7f0055;">with</span></strong></span><span> deptid=76 </span><span class="keyword"><strong><span style="color: #7f0055;">connect</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">by</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">prior</span></strong></span><span> paredeptid=deptid </span></span></li>
</ol>
</div>
</div>
</div>
<p><span class="t18"><span style="color: #ff0000;">====这种方法只是当表里就有一颗树,多棵树怎么办？</span><br></span></p>
</div>
<p> </p>
<div class="quote_div">
<div>Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。</div>
<div>创建示例表：</div>
<div>CREATE TABLE TBL_TEST<br>(<br> ID NUMBER,<br> NAME VARCHAR2(100 BYTE),<br> PID NUMBER DEFAULT 0<br>);</div>
<div> </div>
<div>插入测试数据：</div>
<div>INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');<br>INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');<br>INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');<br>INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');<br>INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');</div>
<div> </div>
<div>从Root往树末梢递归</div>
<div>select * from TBL_TEST<br> start with id=1<br> connect by prior id = pid</div>
<div> </div>
<div>
<div>从末梢往树ROOT递归</div>
<div>select * from TBL_TEST<br> start with id=5<br> connect by prior pid = id</div>
<div>=====</div>
<div>
<h1>对于oracle进行简单树查询(递归查询)</h1>
<p> </p>
<table border="0"><tbody>
<tr>
<td>DEPTID</td>
<td>PAREDEPTID</td>
<td>NAME</td>
</tr>
<tr>
<td>NUMBER</td>
<td>NUMBER</td>
<td>CHAR (40 Byte)</td>
</tr>
<tr>
<td>部门id</td>
<td>父部门id(所属部门id)</td>
<td>部门名称</td>
</tr>
</tbody></table>
<p> </p>
<p>通过子节点向根节点追朔.</p>
<div class="dp-highlighter">
<div class="bar">
<div class="tools">Sql代码 <a title="复制代码" href="/topic/191016"><img src="/images/icon_copy.gif" alt="复制代码"></a>
</div>
</div>
<ol class="dp-sql">
<li><span><span class="keyword"><strong><span style="color: #7f0055;">select</span></strong></span><span> * </span><span class="keyword"><strong><span style="color: #7f0055;">from</span></strong></span><span> persons.dept start </span><span class="keyword"><strong><span style="color: #7f0055;">with</span></strong></span><span> deptid=76 </span><span class="keyword"><strong><span style="color: #7f0055;">connect</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">by</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">prior</span></strong></span><span> paredeptid=deptid </span></span></li>
</ol>
</div>
<pre name="code" class="sql"> select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid </pre>
<p> </p>
<p>通过根节点遍历子节点. </p>
<div class="dp-highlighter">
<div class="bar">
<div class="tools">Sql代码 <a title="复制代码" href="/topic/191016"><img src="/images/icon_copy.gif" alt="复制代码"></a>
</div>
</div>
<ol class="dp-sql">
<li><span><span class="keyword"><strong><span style="color: #7f0055;">select</span></strong></span><span> * </span><span class="keyword"><strong><span style="color: #7f0055;">from</span></strong></span><span> persons.dept start </span><span class="keyword"><strong><span style="color: #7f0055;">with</span></strong></span><span> paredeptid=0 </span><span class="keyword"><strong><span style="color: #7f0055;">connect</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">by</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">prior</span></strong></span><span> deptid=paredeptid </span></span></li>
</ol>
</div>
<pre name="code" class="sql">select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid </pre>
<p> </p>
<p>可通过level 关键字查询所在层次. </p>
<div class="dp-highlighter">
<div class="bar">
<div class="tools">Sql代码 </div>
</div>
<ol class="dp-sql">
<li><span><span class="keyword"><strong><span style="color: #7f0055;">select</span></strong></span><span> a.*,</span><span class="keyword"><strong><span style="color: #7f0055;">level</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">from</span></strong></span><span> persons.dept a start </span><span class="keyword"><strong><span style="color: #7f0055;">with</span></strong></span><span> paredeptid=0 </span><span class="keyword"><strong><span style="color: #7f0055;">connect</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">by</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">prior</span></strong></span><span> deptid=paredeptid </span></span></li>
</ol>
</div>
<pre name="code" class="sql">select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid </pre>
<p> </p>
<p>再次复习一下:start with ...connect by 的用法，<strong> <em><span style="text-decoration: underline;">start with</span></em></strong> 后面所跟的就是就是<span style="color: #ff0000;"><strong>递归的种子</strong></span>。 </p>
<p><span style="color: #ff0000;">递归的种子也就是递归开始的地方</span> connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询； </p>
<p>connect by prior 后面所放的字段是有关系的，<strong><span style="text-decoration: underline;">它指明了查询的方向</span></strong>。 </p>
<p>练习： 通过子节点获得顶节点 </p>
<div class="dp-highlighter">
<div class="bar">
<div class="tools">Sql代码 <a title="复制代码" href="/topic/191016"><img src="/images/icon_copy.gif" alt="复制代码"></a>
</div>
</div>
<ol class="dp-sql">
<li><span><span class="keyword"><strong><span style="color: #7f0055;">select</span></strong></span><span> FIRST_VALUE(deptid) OVER (</span><span class="keyword"><strong><span style="color: #7f0055;">ORDER</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">BY</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">LEVEL</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">DESC</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">ROWS</span></strong></span><span> UNBOUNDED PRECEDING) </span><span class="keyword"><strong><span style="color: #7f0055;">AS</span></strong></span><span> firstdeptid </span><span class="keyword"><strong><span style="color: #7f0055;">from</span></strong></span><span> persons.dept start </span><span class="keyword"><strong><span style="color: #7f0055;">with</span></strong></span><span> deptid=76 </span><span class="keyword"><strong><span style="color: #7f0055;">connect</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">by</span></strong></span><span> </span><span class="keyword"><strong><span style="color: #7f0055;">prior</span></strong></span><span> paredeptid=deptid </span></span></li>
</ol>
</div>
</div>
</div>
<p><span class="t18"><span style="color: #ff0000;">====这种方法只是当表里就有一颗树,多棵树怎么办？</span><br></span></p>
</div>
<p> </p>
3 楼
alan6288
2009-05-07
不错，我还不知道Oracle有这样的功能呢，上次为了查询一个系统几百个功能模块的关系，搞了N多子查询呢。
值得收藏学习啊
值得收藏学习啊
2 楼
511543417
2009-05-02
好贴。我什么时候才能写出这样的帖子呢。看来我的努力了！！！··
1 楼
zhanght327
2009-02-13
谢谢楼主 学习了
发表评论
-
SQL数据库设计经验
2009-04-07 10:00 993SQL数据库设计经验 2009- ... -
用户sa登陆失败，原因“未与信任的SQL server连接相
2009-04-03 15:47 4127用户sa登陆失败，原因"未与信任的SQL serve ... -
oralce plsql工作中的吸取
2009-01-12 10:14 956v_prior_wip extend_wip%rowtyp ... -
oracle 远程共享
2008-12-18 10:30 951服务器想共享客户端的表 例如我自己是服务器17_test我想访 ... -
TRUNCATE TABLE----delete
2008-12-17 09:19 1919删除表中的所有行，而不记录单个行删除操作。TRUNCATE ... -
oracle 用PLSQL Developer(或者导出命令)怎么导出
2008-12-16 15:09 6385oracle 用PLSQL Developer(或者导出命令) ... -
带参数的CURSOR如何使用?
2008-12-16 10:01 4332--定义cursor cursor cuhdr(run_ ... -
REF CURSOR 小结
2008-12-05 09:40 3467REF CURSOR 小结 利用REF CURSOR，可以在程 ... -
怎么在oracle数据库格式的时间上加60分钟?
2008-12-03 14:42 1577这样 比如你要加上40分钟，就在日期型数据后面加上 分钟数/6 ... -
ORACLE集合常用方法
2008-11-25 10:38 2053from：http://blog.itpub.net ... -
index-by集合问题
2008-11-25 08:57 962包头： create or replace package a ... -
怎么在sqlplus调用输入参数为集合类型的包函数?---（index-by集合）
2008-11-24 16:27 1046怎么在sqlplus调用输入参数为集合类型的包函数?---（i ... -
怎么在sqlplus调用输入参数为集合类型的包函数?---（index-by集合）
2008-11-21 15:07 1124create or replace package ... -
Oracle三种集合数据类型的比较
2008-11-21 11:34 1470PL/SQL中没有数组的概念，他的集合数据类型和数组是相似的。 ... -
oracle常用命令集合(随记)
2008-11-21 11:30 1045SQL常用关键字(#:表名 ...:字段)sqlplus s ... -
FORALL与BULKCOLLECT的使用方法
2008-11-20 14:08 11821．使用FORALL比FOR效率高，因为前者只切换一次上下文， ... -
pls_integer类型
2008-11-20 13:55 2892今天在看一个触发器代码的时候碰到了一个pls_integer类 ... -
SQL Server中的分组和求和的问题
2008-11-18 16:40 3067表 物品 数量 类型 笔记本电脑 100 进货 台试机 100 ... -
MyEclipse中配置SQL server数据源
2008-11-09 15:53 38251、首先在控制面板里建立你的数据源test，必须用选用： ... -
在oracle 9i里主键的自动增长(补充)
2008-11-09 15:45 824create table ADDRESSBOOK( ...
相关推荐
Oracle start with.connect by prior子句实现递归查询
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
在Oracle中用Start with...Connect By子句递归查询
oracle菜单树查询 使用实例 使用START WITH CONNECT BY PRIOR子句实现递归查询
本文介绍Oracle中使用START WITH...CONNECT BY PRIOR子句实现递归查询树形结构的方法，小伙伴们可以参考一下。
Startwith...ConnectBy子句递归查询一般用于一个表维护树形结构的应用。 创建示例表：CREATETABLETBL_TEST(IDNUMBER,NAMEVARCHAR2(100BYTE),PIDNUMBERDEFAULT0);插入测试数据：INSERTINTOTBL_TEST(I
Oracle中的select语句可以用START WITH…CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是： select * from tablename start with cond1 connect by cond2 where cond3; ...
10.3 递归子查询 273 10.3.1 一个CONNECT BY的例子 274 10.3.2 使用RSF的例子 275 10.3.3 RSF的限制条件 276 10.3.4 与CONNECT BY的不同点 276 10.4 复制CONNECT BY的功能 277 10.4.1 LEVEL伪列 278 10.4.2 ...