Oracle中Start with...Connect By理解及用法

Oracle中Start with...Connect By实现部门多级递归理解及用法

 

其基本语法是：

select ... from tablename start with cond1

connect by cond2

where cond3;

简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段: id,parentid,那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。用上述语法的查询可以取得这棵树的所有记录。

其中COND1是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。

COND2是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR ID=PRAENTID就是说上一条记录的ID是本条记录的PRAENTID，即本记录的父亲是上一条记录

COND3是过滤条件，用于对返回的所有记录进行过滤

例子:

创建示例表：

CREATE TABLE TBL_TEST

(

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');

插入数据后：

 

Oracle中Start with...Connect By理解及用法

 

其基本语法是： select ... from tablename start with cond1 connect by cond2 where cond3; 简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段: id,parentid,那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。用上述语法的查询可以取得这棵树的所有记录。 其中COND1是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。 COND2是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR ID=PRAENTID就是说上一条记录的ID是本条记录的PRAENTID，即本记录的父亲是上一条记录。 COND3是过滤条件，用于对返回的所有记录进行过滤。 例子: 创建示例表： CREATE TABLE TBL_TEST ( 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'); 插入数据后：   从Root往树末梢递归(取所有点) select * from TBL_TEST start with id=1 connect by prior id = pid 显示结果： 从末梢往树ROOT递归(只取根结点,根结点的根结点......) select * from TBL_TEST start with id=5 connect by prior pid = id 结果：     注： 　prior 写在哪个字段那边，那么就用start with 这条记录的该项值　与　别的记录的另一个字段的值去关联; start with 可以不写，默认为所有记录 例如 select * from TBL_TEST start with id=5 connect by prior pid = id 因为pid=2 与id=5 在本数据表中为同一条数据， 因此可以理解为： select * from TBL_TEST start with pid=2 connect by prior pid = id (个人认为这样更好理解)

 ****************************************************

此语句可以直接分层次显示

SELECT lpad('|-',(level-1)*4,'|-')||lpad('『',2)||di_name||rpad('』',2)
di_name,di_id
FROM t_dictionary_link CONNECT BY PRIOR di_id=di_protypeid
START WITH di_id=-1

 

|-|-『公司架构』                                                                         2
|-|-|-|-『芯码科技』                                                                     3
|-|-|-|-|-|-『开发部』                                                                   4
|-|-|-|-|-|-|-|-『JAVA组』                                                              11
|-|-|-|-|-|-|-|-『.NET组』                                                             102
|-|-|-|-|-|-『销售部』                                                                 261
|-|-|-|-|-|-『客服部』