START WITH and CONNECT BY in Oracle

start with .. connect by clause can be used to select data that has a hierarchical relationship. Usually, it is some sort of parent child relationship like supervisor and an employee.

Let’s assume we have a table called employees. I would like to know employees that directly report to supervisor Id 1122456 –simple, right?

SELECT emplid, supervisor_id
  FROM ps_employees
  WHERE supervisor_id = '1122456';

EMPLID  SUPERVISOR_ID
0119676  1122456
0112356  1122456
0120022  1122456

 

Now I would like to know all employees that report to supervisor Id 1122456 (directly on indirectly).

SELECT     emplid, supervisor_id
      FROM ps_employees
START WITH supervisor_id = '1122456'
CONNECT BY PRIOR emplid = supervisor_id;

EMPLID  SUPERVISOR_ID
0119676  1122456
0112356  1122456
0120022  1122456
0120033  0120022

 

Notice that with the 1st sql we had 3 rows total while using Start With gave us 4 rows back. If you look at the last row on the 2nd select, you notice that employee 0120033 reports to 0120022 who in return reports to 1122456.

 

 

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。

创建示例表：

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

 

转载于:https://www.cnblogs.com/GoDevil/archive/2008/08/08/1263846.html