转 。。。。。。。。。。。。。。。。。。。。。。。。。
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
prompt PL/SQL Developer import file
prompt Created on 2010年12月23日 by qcy
set feedback off
set define off
prompt Dropping TBL_TEST...
drop table TBL_TEST cascade constraints;
prompt Creating TBL_TEST...
create table TBL_TEST
(
ID NUMBER not null,
NAME VARCHAR2(100),
PID NUMBER default 0
)
tablespace STD
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table TBL_TEST
add constraint PK_TBK_TEST primary key (ID)
using index
tablespace STD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
prompt Disabling triggers for TBL_TEST...
alter table TBL_TEST disable all triggers;
prompt Loading TBL_TEST...
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);
commit;
prompt 5 records loaded
prompt Enabling triggers for TBL_TEST...
alter table TBL_TEST enable all triggers;
set feedback on
set define on
prompt Done.
从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) | 部门名称 |
通过子节点向根节点追朔.
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
通过根节点遍历子节点.
可通过level 关键字查询所在层次.
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 后面所放的字段是有关系的,它指明了查询的方向。
练习: 通过子节点获得顶节点
- 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