oracle 递归查询 CONNECT BY、START WITH、CONNECT_BY_ROOT、CONNECT_BY_ISLEAF、SYS_CONNECT_BY_PATH

来源：互联网发布：js获取点击事件编辑：程序博客网时间：2024/05/18 00:54

转载自：http://blog.csdn.net/feier7501/article/details/21815691

创建表，初始化数据;

[sql] view plaincopyprint?

CREATE TABLE TB_COMPANY
(
COMPANY_ID INTEGER PRIMARY KEY,
COMPANY VARCHAR2(256),
UP_COMPANYID INTEGER
);
INSERT INTO TB_COMPANY VALUES (0, '总公司', NULL);
INSERT INTO TB_COMPANY VALUES (1, '北京分公司', 0);
INSERT INTO TB_COMPANY VALUES (2, '上海分公司', 0);
INSERT INTO TB_COMPANY VALUES (3, '海淀区分部', 1);
INSERT INTO TB_COMPANY VALUES (4, '东城区分部', 1);
INSERT INTO TB_COMPANY VALUES (5, '黄埔区分部', 2);
INSERT INTO TB_COMPANY VALUES (6, '静安区分部', 2);
COMMIT;

CREATE TABLE TB_COMPANY(  COMPANY_ID INTEGER PRIMARY KEY,  COMPANY VARCHAR2(256),  UP_COMPANYID INTEGER);INSERT INTO TB_COMPANY VALUES (0, '总公司', NULL);INSERT INTO TB_COMPANY VALUES (1, '北京分公司', 0);INSERT INTO TB_COMPANY VALUES (2, '上海分公司', 0);INSERT INTO TB_COMPANY VALUES (3, '海淀区分部', 1);INSERT INTO TB_COMPANY VALUES (4, '东城区分部', 1);INSERT INTO TB_COMPANY VALUES (5, '黄埔区分部', 2);INSERT INTO TB_COMPANY VALUES (6, '静安区分部', 2);COMMIT;

全部数据：

递归查询SQL：

[sql] view plaincopyprint?

SELECT (RPAD(' ', 2*(LEVEL-1), '-' ) || COMPANY) COMPANY_NAME, CONNECT_BY_ROOT COMPANY, CONNECT_BY_ISLEAF, LEVEL , SYS_CONNECT_BY_PATH(COMPANY, '/')
FROM TB_COMPANY
START WITH UP_COMPANYID IS NULL
CONNECT BY PRIOR COMPANY_ID = UP_COMPANYID;

SELECT (RPAD(' ', 2*(LEVEL-1), '-' ) || COMPANY) COMPANY_NAME, CONNECT_BY_ROOT COMPANY, CONNECT_BY_ISLEAF, LEVEL , SYS_CONNECT_BY_PATH(COMPANY, '/')FROM TB_COMPANYSTART WITH UP_COMPANYID IS NULLCONNECT BY PRIOR COMPANY_ID = UP_COMPANYID;

结果：

说明：

1. CONNECT_BY_ROOT 返回当前节点的最顶端节点
2. CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
3. LEVEL 伪列表示节点深度
4. SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔

递归查询SQL：

[sql] view plaincopyprint?

SELECT * FROM TB_COMPANY START WITH COMPANY_ID = 1 CONNECT BY PRIOR COMPANY_ID = UP_COMPANYID;

SELECT * FROM TB_COMPANY START WITH COMPANY_ID = 1 CONNECT BY PRIOR COMPANY_ID = UP_COMPANYID;

结果：

0 0