oracle 的start with connect by prior 的用法_mybatis里使用start with connect by prior-优快云博客

本文通过四个示例展示了如何使用Oracle SQL的sys_connect_by_path函数来遍历部门层级结构并构造路径。包括基本遍历、获取最长路径、逆向遍历及获取最长逆向路径的具体实现。

1. select sys_connect_by_path(t.dept_id,'/') from syc_smg_dpt t start with t.dept_id='RT_DISTRICT_JN_01' connect by prior dept_id = parent_dept_id -------以'RT_DISTRICT_JN_01' 遍历其作为父节点的所有值

结果:

/RT_DISTRICT_JN_01
/RT_DISTRICT_JN_01/RT_GROUP_JN_CAR1
/RT_DISTRICT_JN_01/RT_GROUP_JN_CAR2
/RT_DISTRICT_JN_01/RT_GROUP_JN_XYDM1_1
/RT_DISTRICT_JN_01/RT_GROUP_JN_XYDM1_2
/RT_DISTRICT_JN_01/RT_GROUP_JN_XYDM2_1
/RT_DISTRICT_JN_01/RT_GROUP_JN_XYDM3_1

2. select max(sys_connect_by_path(t.dept_id,'/')) from syc_smg_dpt t start with t.dept_id = 'RT_DISTRICT_JN_01' connect by prior dept_id = parent_dept_id; ---取最大值

结果: /RT_DISTRICT_JN_01/RT_GROUP_JN_XYDM3_1

3. select sys_connect_by_path(t.dept_id,'/') from syc_smg_dpt t start with t.dept_id='RT_DISTRICT_JN_01' connect by prior parent_dept_id = dept_id;

结果: /RT_DISTRICT_JN_01
/RT_DISTRICT_JN_01/RT_CHARGER_JN_01
/RT_DISTRICT_JN_01/RT_CHARGER_JN_01/RT_MANAGER_01
/RT_DISTRICT_JN_01/RT_CHARGER_JN_01/RT_MANAGER_01/PA012_RT_SYSTEM_01

4. select max(sys_connect_by_path(t.dept_id,'/')) from syc_smg_dpt t start with t.dept_id = 'RT_DISTRICT_JN_01' connect by prior parent_dept_id = dept_id;

结果：/RT_DISTRICT_JN_01/RT_CHARGER_JN_01/RT_MANAGER_01/PA012_RT_SYSTEM_01