欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

oracle递归查询

程序员文章站 2022-07-14 21:21:24
...
转自:虫二的专栏 http://blog.csdn.net/xzknet/archive/2008/05/06/2404559.aspx
Select * from …. Where [结果过滤条件语句]

Start with  [and起始条件过滤语句]

Connect by prior [and中间记录过滤条件语句]

一、Start with.........Connect By 子句

START WITH specifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row
二、示例

1. 创建测试表:
CREATE TABLE TBL_TEST
(
  ID    NUMBER,
  NAME  VARCHAR2(100 BYTE),
  PID   NUMBER                                  DEFAULT 0
);


2.插入测试数据:
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','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');



3.上述单表情况一般用于维护树型结构的应用

从Root往树末梢递归
select * from TBL_TEST t 
start with t.id=1
connect by prior t.id = t.pid


从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id


start with 后面跟着树根,定义了树开始显示的起点位置,如上面两种查询,不管从树根到树顶,还是从树顶到树根。

5.排除单一体及其分支

select t.* from TBL_TEST t
start with t.id=1
connect by prior pid = id
and id != 2


这个语句表示从id为2的开始,包括后面的子分支,全部排除

注意:这里和where条件的过虑方式不一样,where条件只是把相应的某条不符合要求的记录排除。