Oracle 递归查询 start with... connect by prior...

Oracle 递归查询 start with… connect by prior…

-- 语法
select column from table 
start with ...
connect by prior ...

Oracle 递归查询是以start with 后的条件筛选出主干，然后通过主干结合connect by 条件查找分支，最终形成树状结构。下面通过示例进行说明：

-- 构建环境
CREATE TABLE T_TEST_AREA(
	ID INT,
	PID INT,
	NAME VARCHAR2(20),
	DELETED INT,
	CHECKED INT
);

DELETE FROM T_TEST_AREA WHERE 1=1;
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(1, -1, '长沙市', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(2, -1, '深圳市', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(3, 1, '开福区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(4, 1, '雨花区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(5, 1, '岳麓区', 0, 0);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(6, 2, '宝安区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(7, 2, '南山区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(8, 2, '福田区', 0, 0);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(9, 4, '黎托街道', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(10, 4, '东山街道', 0, 1);

-- 查询
ID|PID|NAME|DELETED|CHECKED|
--|---|----|-------|-------|
 1| -1|长沙市 |      0|      1|
 2| -1|深圳市 |      0|      1|
 3|  1|开福区 |      0|      1|
 4|  1|雨花区 |      0|      1|
 5|  1|岳麓区 |      0|      0|
 6|  2|宝安区 |      0|      1|
 7|  2|南山区 |      0|      1|
 8|  2|福田区 |      0|      0|
 9|  4|黎托街道|      0|      1|
10|  4|东山街道|      0|      1|

1、 从上往下查找

SELECT * FROM T_TEST_AREA 
START WITH PID = -1       -- 筛选主干
CONNECT BY PRIOR ID = PID; -- prior修饰的是主干的字段，筛选PID=主干ID的数据作为分支数据

-- 此种写法结果同上，但不建议使用这种写法
SELECT * FROM T_TEST_AREA START WITH PID = -1 CONNECT BY PID = PRIOR ID;

-- result
ID|PID|NAME|DELETED|CHECKED|
--|---|----|-------|-------|
 1| -1|长沙市 |      0|      1|
 3|  1|开福区 |      0|      1|
 4|  1|雨花区 |      0|      1|
 9|  4|黎托街道|      0|      1|
10|  4|东山街道|      0|      1|
 5|  1|岳麓区 |      0|      0|
 2| -1|深圳市 |      0|      1|
 6|  2|宝安区 |      0|      1|
 7|  2|南山区 |      0|      1|
 8|  2|福田区 |      0|      0|

2、 从下往上查找

SELECT * FROM T_TEST_AREA START WITH ID = 10 CONNECT BY PRIOR PID = ID;
SELECT * FROM T_TEST_AREA START WITH ID = 10 CONNECT BY ID = PRIOR PID;

-- result
ID|PID|NAME|DELETED|CHECKED|
--|---|----|-------|-------|
10|  4|东山街道|      0|      1|
 4|  1|雨花区 |      0|      1|
 1| -1|长沙市 |      0|      1|

3、通用递归（无法形成树状顺序）

WITH ALLSUB(ID, PID, NAME, DELETED, CHECKED) AS(
-- 主干
SELECT ID, PID, NAME, DELETED, CHECKED FROM T_TEST_AREA WHERE PID = -1
UNION ALL
-- 分支
SELECT A.ID, A.PID, A.NAME, A.DELETED, A.CHECKED FROM T_TEST_AREA A, ALLSUB B WHERE A.PID = B.ID
)
SELECT ID, PID, NAME, DELETED, CHECKED FROM ALLSUB;

-- result
ID|PID|NAME|DELETED|CHECKED|
--|---|----|-------|-------|
 1| -1|长沙市 |      0|      1|
 2| -1|深圳市 |      0|      0|
 3|  1|开福区 |      0|      1|
 4|  1|雨花区 |      0|      1|
 5|  1|岳麓区 |      0|      0|
 6|  2|宝安区 |      0|      1|
 7|  2|南山区 |      0|      1|
 8|  2|福田区 |      0|      0|
 9|  4|黎托街道|      0|      1|
10|  4|东山街道|      0|      1|