connect by start with 实现tree结构的语法

在业务系统经常会看到有层级关系的表设计，比如行政区域，组织结构等。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 对结果集进行组内排序，这样结果集看起来才有意义。