Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表:
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
(
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');
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
*
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
*
FROM
TBL_TEST START
WITH id=5 CONNECT BY prior pid = id
可通过level 关键字查询所在层次
SELECT
*,
level
FROM
TBL_TEST START
WITH id=1 CONNECT BY prior id = pid
*,
level
FROM
TBL_TEST START
WITH id=1 CONNECT BY prior id = pid
start with ...connect by 的用法,
start with 后面所跟的就是就是
递归的种子。
递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省:则只能查询到符合条件的起始行,并不进行递归查询;
connect by prior 后面所放的字段是有关系的,它指明了查询的方向。