Oracle：connect_by_isleaf

最新推荐文章于 2023-10-11 15:47:20 发布

转载最新推荐文章于 2023-10-11 15:47:20 发布 · 2k 阅读

Oracle 专栏收录该内容

538 篇文章

订阅专栏

本文介绍了一种使用 Oracle SQL 的 CONNECT BY 和 SYS_CONNECT_BY_PATH 函数来遍历树形结构数据的方法。通过具体 SQL 语句示例，解释了如何设置起始节点、判断节点类型以及构建路径。

select connect_by_isleaf isleaf,
            tb.switch_type_id,
            sys_connect_by_path(tb.package_id, '|') path,
            tb.record_type,
            tb.element_offset,
            tb.element_length,
            tb.element_encode_type,
            tb.element_align,
            tb.mask,
            tb.asn_type
            from (select b.switch_type_id,
                    b.package_id,
                    b.element_offset,
                    b.element_length,
                    b.element_encode_type,
                    nvl(b.element_align, 'L') element_align,
                    b.record_type,
                    nvl(b.mask, 'LH') mask,
                    b.asn_type,
                    b.switch_type_id || '|' || b.package_level || '|' || b.package_id level_child,
                    b.switch_type_id || '|' || (b.package_level - 1) || '|' || b.parent_package_id level_parent
                 from switch_asn_record b
                 ) tb
             start with tb.level_parent like '%|0|-1'
             connect by prior tb.level_child = tb.level_parent;

1、connect_by_isleaf是否是子节点；
2、sys_connect_by_path(tb.package_id, '|')   ==> |xxx|xxx|xxx
3、start with tb.level_parent like '%|0|-1' ==> 树的遍历以从什么开始
4、connect by prior tb.level_child = tb.level_parent; ==>此层遍历之后，再将child作为parent，继续往下遍历