时间：2021-07-01 10:21:17 帮助过：7人阅读
| 1 2 |
[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition
|
The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts).
说明：
1. START WITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。
2. 当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。此时，需要在connect by后面加上NOCYCLE关键字。同时，可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。 connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用
接下来，用一些示例来说明“CONNECT BY”的用法。
创建一个部门表，这个表有三个字段，分别对应部门ID，部门名称，以及上级部门ID
?| 1 2 3 4 5 6 7 8 |
-- Create table
create table DEP
(
DEPID number(10) not null,
DEPNAME varchar2(256),
UPPERDEPID number(10)
)
;
|
初始化一些数据
?| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, ‘总经办‘, null);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, ‘开发部‘, 0);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, ‘测试部‘, 0);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, ‘Sever开发部‘, 1);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, ‘Client开发部‘, 1);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, ‘TA测试部‘, 2);
1 row inserted
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, ‘项目测试部‘, 2);
1 row inserted
SQL> commit;
Commit complete
|
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> SELECT * FROM DEP;
DEPID DEPNAME UPPERDEPID
----------- -------------------------------------------------------------------------------- -----------
0 General Deparment
1 Development 0
2 QA 0
3 Server Development 1
4 Client Development 1
5 TA 2
6 Porject QA 2
7 rows selected
|
现在我要根据“CONNECT BY”来实现树状查询结果
?