测试数据: DM_FPKF 包括三个字段FPKF_DM(库房代码)、SJ_FPKF_DM(上级库房代码)、ZKFBZ(主库房标志)
图片为测试结果结果
CONNECT BY 的用法:
--以23415000000000为根节点,深度优先遍历
select level,a.* from fpglhx.dm_fpkf a
start with a.fpkf_dm = '234150000000000'
connect by prior a.fpkf_dm = a.sj_fpkf_dm;
--以23415000000000为根节点,深度优先遍历(不包括根节点)
select level,a.* from fpglhx.dm_fpkf a
start with a.sj_fpkf_dm = '234150000000000'
connect by prior a.fpkf_dm = a.sj_fpkf_dm;
--以23415000000000为子节点,逐级向上寻找遍历父节点
select level,a.* from fpglhx.dm_fpkf a
start with a.fpkf_dm = '234150000000000'
connect by prior a.sj_fpkf_dm = a.fpkf_dm;
--以23415000000000为父节点,逐级向上遍历
select level,a.* from fpglhx.dm_fpkf a
start with a.sj_fpkf_dm = '234150000000000'
connect by prior a.sj_fpkf_dm = a.fpkf_dm;
--以23415000000000为根节点,广度优先遍历
select level,a.fpkf_dm,a.sj_fpkf_dm,a.zkfbz from fpglhx.dm_fpkf a
start with a.fpkf_dm = '234150000000000'
connect by prior a.fpkf_dm = a.sj_fpkf_dm
order by level;
--寻找叶子节点
SELECT
connect_by_isleaf "是否是叶子节点",
a.fpkf_dm,a.sj_fpkf_dm,a.zkfbz
FROM fpglhx.dm_fpkf a
start with a.fpkf_dm = '234150000000000'
connect by prior a.fpkf_dm = a.sj_fpkf_dm;
--遍历路径,根节点
SELECT LEVEL "等级",
CONNECT_BY_ISLEAF "是否是叶子节点",
CONNECT_BY_ROOT a.fpkf_dm ENAME,
LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(a.fpkf_dm, '=>'),
a.*
FROM fpglhx.dm_fpkf a
connect by prior a.fpkf_dm = a.sj_fpkf_dm;
节点234150000000000到节点234152118000002的路径
SELECT
SYS_CONNECT_BY_PATH(a.fpkf_dm,'=>')
FROM fpglhx.dm_fpkf a
where a.fpkf_dm = '234152118000002'
connect by prior a.fpkf_dm = a.sj_fpkf_dm
start with a.fpkf_dm = '234150000000000';