文章目录
一、准备数据
- 创建表
CREATE TABLE TEST_ORG
(
ID VARCHAR2(64) NOT NULL PRIMARY KEY,
NAME VARCHAR2(200),
PARTEN_ID VARCHAR2(64)
);
comment on column TEST_ORG.ID is '主键';
comment on column TEST_ORG.NAME is '名称';
comment on column TEST_ORG.PARTEN_ID is '父级id';
- 插入数据
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('1', '北京市', '0');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('4', '东城区', '1');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('6', '天安门', '4');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('5', '延庆区', '1');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('7', '八达岭', '5');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('2', '天津市', '0');
INSERT INTO EXECUTE_DB.TEST_ORG (ID, NAME, PARTEN_ID) VALUES('3', '上海市', '0');
二、基本使用
1、获取完整的树结构
select * from TEST_ORG start with PARTEN_ID = 0 connect by prior id = PARTEN_ID;
执行结果：
id name parten_id
1 北京市 0
4 东城区 1
6 天安门 4
5 延庆区 1
7 八达岭 5
2 天津市 0
3 上海市 0
2、获取指定节点的全部子节点（包含指定节点）
例：获取北京市的全部子级，传入id=1
select * from TEST_ORG start with id = 1 connect by prior id = PARTEN_ID;
执行结果：
id name parten_id
1 北京市 0
4 东城区 1
6 天安门 4
5 延庆区 1
7 八达岭 5
提示：会返回当前数据及全部子级节点。
3、获取指定节点的全部子节点（不包含指定节点）
例：获取北京市的全部子级，传入PARTEN_ID=1
select * from TEST_ORG start with PARTEN_ID = 1 connect by prior id = PARTEN_ID;
执行结果：
id name parten_id
4 东城区 1
6 天安门 4
5 延庆区 1
7 八达岭 5
提示：只会返回当前数据的全部子级节点。
4、获取指定节点的全部父节点（包含指定节点）
例：获取天安门的全部父级
select * from TEST_ORG start with id = 6 connect by prior PARTEN_ID = id;
执行结果：
id name parten_id
6 天安门 4
4 东城区 1
1 北京市 0
提示：会返回当前数据以及全部父级节点。
三、level函数
level字段为oracle特有的层级字段，可以通过level字段查询指定的层级。
例：获取天安门的全部父级上层级2的一层
select t.*,level from TEST_ORG t WHERE level = 2 start with id = 6 connect by prior PARTEN_ID = id;
执行结果：
id name parten_id
4 东城区 1 2
提示：只会返回当前数据以及全部父级节点的层级为2的数据。
四、获取完整的全树路径
例：获取北京市以及全部子级，指定id=1表示北京市。
select t.*,sys_connect_by_path(name, '->') text from TEST_ORG t start with id = 1 connect by prior id = PARTEN_ID;
执行结果：
id name parten_id text
1 北京市 0 ->北京市
4 东城区 1 ->北京市->东城区
6 天安门 4 ->北京市->东城区->天安门
5 延庆区 1 ->北京市->延庆区
7 八达岭 5 ->北京市->延庆区->八达岭
提示：结合substr函数去除指定内容。
例如：去除最开始的->符号
select t.*,substr(sys_connect_by_path(name, '->'), 3) text from TEST_ORG t start with id = 1 connect by prior id = PARTEN_ID;
执行结果：
id name parten_id text
1 北京市 0 北京市
4 东城区 1 北京市->东城区
6 天安门 4 北京市->东城区->天安门
5 延庆区 1 北京市->延庆区
7 八达岭 5 北京市->延庆区->八达岭
