sys_connect_by_path函数用法

iteye_15396

于 2012-10-25 10:27:36 发布

阅读量520

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle基础

本文链接：https://blog.youkuaiyun.com/iteye_15396/article/details/82437367

oracle基础专栏收录该内容

27 篇文章

订阅专栏

本文介绍 Oracle SQL 中的 sys_connect_by_path 函数，并通过示例演示如何使用该函数来构建层级结构，同时展示了如何判断叶子节点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sys_connect_by_path函数是用来返回组成层级的直到当前行的值。

例如：

select *
  from (select ltrim(sys_connect_by_path(id, ','), ',') id,
               ltrim(sys_connect_by_path(name, ','), ',') name,
               level as lvl,
               lpad(' ', 2 * (level - 1)) || id,
               lpad(' ', 2 * (level - 1)) || name,
               lead(level) over(order by id) leadlvlorder,
               case
                 when (level - lead(level) over(order by id)) < 0 then
                  0
                 else
                  1
               end isleaf
          from table_name c
         start with id = 0
        connect by prior c.id = c.parent_id)
 where isleaf = 1;

CONNECT_BY_ISLEAF 也可以判断叶子节点,如下：

select *
  from (select ltrim(sys_connect_by_path(id, ','), ',') id,
               ltrim(sys_connect_by_path(name, ','), ',') name,
               level as lvl,
               lpad(' ', 2 * (level - 1)) || id,
               lpad(' ', 2 * (level - 1)) || name,
               CONNECT_BY_ISLEAF isLeaf
          from table_name c
         start with id = 0
        connect by prior c.id = c.parent_id)
 where isleaf = 1;