Oracle递归查询-start with connect by prior 用法

创建测试数据：

CREATE TABLE TEST_DEPT(ID varchar2(10) NOT NULL,NAME varchar2(200),PID varchar2(10));

INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1000', '总公司', NULL);
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1001', '北京分公司', '1000');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1002', '上海分公司', '1000');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1003', '北京研发部', '1001');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1004', '北京财务部', '1001');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1005', '北京市场部', '1001');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1006', '北京研发一部', '1003');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1007', '北京研发二部', '1003');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1008', '北京研发一部一小组', '1006');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1009', '北京研发一部二小组', '1006');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1010', '北京研发二部一小组', '1007');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1011', '北京研发二部二小组', '1007');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1012', '北京市场一部', '1005');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1013', '上海研发部', '1002');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1014', '上海研发一部', '1013');
INSERT INTO TEST_DEPT(ID, NAME, PID) VALUES ('1015', '上海研发二部', '1013');

commit;

 

在 Oracle 中是通过 start with connect by prior 语法来实现递归查询的。

按照 prior 关键字在子节点端还是父节点端，以及是否包含当前查询的节点，共分为四种情况。

prior 在子节点端（向下递归）

第一种情况：

start with 子节点id = ' 查询节点 ' connect by prior 子节点id = 父节点id

select * from TEST_DEPT start with ID='1001' connect by prior ID=PID;

 

这里，按照条件 id='1001' 对当前节点以及它的子节点递归查询。查询结果包含自己及所有子节点。

第二种情况：

start with 父节点id= ' 查询节点 ' connect by prior 子节点id = 父节点 id

select * from TEST_DEPT start with PID='1001' connect by prior ID=PID;

这里，按照条件 pid='1001' 对当前节点的所有子节点递归查询。查询结果只包含它的所有子节点，不包含自己。

 

prior 在父节点端（向上递归）

第三种情况：

start with 子节点id= ' 查询节点 ' connect by prior 父节点id = 子节点id

select * from TEST_DEPT start with ID='1001' connect by prior PID=ID;

这里按照条件 id='1001' ，对当前节点及其父节点递归查询。查询结果包括自己及其所有父节点。

第四种情况：

start with 父节点id= ' 查询节点 ' connect by prior 父节点id = 子节点id

select distinct * from TEST_DEPT start with PID='1001' connect by prior PID=ID;

这里按照条件 pid='1001'，对当前节点的第一代子节点以及它的父节点递归查询。查询结果包括自己的第一代子节点以及所有父节点。（包括自己）

总结：

我们只需要记住 prior 的位置在子节点端，就向下递归，在父节点端就向上递归。

开始条件若是子节点的话，自然包括它本身的节点。

开始条件若是父节点的话，则向下递归时，自然不包括当前节点。而向上递归，需要包括当前节点及其第一代子节点。