在业务系统经常会看到有层级关系的表设计，比如行政区域，组织结构等。oracle 提供了connect by start with 来快速处理这种需求。
模拟业务数据，创建如下表结构
-- Create table
create table TMP_DEPARTMENT
(
department_id VARCHAR2(40) not null,
depart_name VARCHAR2(64),
parent_id VARCHAR2(40)
);
插入数据
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('ZONG', '总部', NULL);
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('FEN_1', '分公司1', 'ZONG');
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('FEN_2', '分公司2', 'ZONG');
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('JING_1', '经营部A', 'FEN_1');
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('JING_2', '经营部B', 'FEN_1');
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('JING_3', '经营部C', 'FEN_2');
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('JING_4', '经营部D', 'FEN_2');
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('BM_1', 'A部门', 'ZONG');
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('BM_2', 'B部门', 'ZONG');
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('BM_3', 'C部门', 'ZONG');
Insert into TMP_DEPARTMENT(DEPARTMENT_ID, DEPART_NAME, PARENT_ID)
Values('FEN_BM_1', 'D部门', 'FEN_1');
COMMIT;
connect by start with 的语句如下
SELECT level as level_id,
lpad(' ', 3 * level - 1) || wd.department_id as level_dept_id,
lpad(' ', 3 * level - 1) || wd.depart_name as level_dept_name,
sys_connect_by_path(wd.department_id, '/') as level_dept_id_path,
sys_connect_by_path(wd.depart_name, '/') as level_dept_name_path,
connect_by_root wd.department_id as level_root_dept_id,
connect_by_isleaf as level_node_isleaf, --
'#####',
wd.department_id,
wd.depart_name,
wd.parent_id
FROM tmp_department wd
CONNECT BY
prior wd.department_id = wd.parent_id
start with wd.parent_id is null
order siblings by wd.department_id
;
执行结果如下
简单说明
connect by prior
涉及到 节点的遍历方向，是向上遍历还是向下遍历。
向下遍历(向叶节点遍历)： CONNECT BY prior wd.department_id = wd.parent_id
当 parent = prior id 时，数据库会根据当前的id迭代出parent与该id相同的记录，所以查询的结果是迭代出了所有的子类记录；
向上遍历(向根节点遍历)： CONNECT BY prior wd.parent_id = wd.department_id
而 prior parent = id 时，数据库会跟据当前的parent来迭代出与当前的parent相同的id的记录，所以查询出来的结果就是所有的父类结果。
start with
从哪一行开始往上或者往下迭代，一般是要加这个条件，如果没加，就会有很多干扰行。很好理解吧。
order siblings by :
siblings 关键字 指示oracle 对结果集进行组内排序，这样结果集看起来才有意义。