oracle层级查询(Hierarchical Queries)

最新推荐文章于 2021-09-26 18:27:57 发布

xianzhi558

最新推荐文章于 2021-09-26 18:27:57 发布

阅读量499

点赞数 1

分类专栏：学习笔记文章标签： oracle

学习笔记专栏收录该内容

3 篇文章

订阅专栏

本文介绍Oracle数据库中进行层级查询的方法及常用伪列的功能，通过示例展示了如何使用CONNECT BY和START WITH子句来构建层级结构，并展示如何获取层级、路径等信息。

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

oracle层级查询(Hierarchical Queries)

{ CONNECT BY [ NOCYCLE ] condition [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition
}

oracle官网详解

伪列(Pseudocolumn)

oracle层级查询常用的伪列有：

level:层级
connect_by_isleaf:是否是叶子结点
connect_by_iscycle:是否是导致出现死循环的那个树枝
SYS_CONNECT_BY_PATH( , ):路径
CONNECT_BY_ROOT:根结点
样例(Example )

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager", LEVEL-1 "Pathlen", LTRIM(SYS_CONNECT_BY_PATH(last_name, '/'),'/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id
   ORDER BY "Employee", "Manager", "Pathlen", "Path";

Employee        Manager            Pathlen Path
--------------- --------------- ---------- ------------------------------
Gietz           Higgins                  1 Higgins/Gietz
Gietz           King                     3 King/Kochhar/Higgins/Gietz
Gietz           Kochhar                  2 Kochhar/Higgins/Gietz
Higgins         King                     2 King/Kochhar/Higgins
Higgins         Kochhar                  1 Kochhar/Higgins