oracle层级查询(Hierarchical Queries)

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/GietzGietz           King                     3 King/Kochhar/Higgins/GietzGietz           Kochhar                  2 Kochhar/Higgins/GietzHiggins         King                     2 King/Kochhar/HigginsHiggins         Kochhar                  1 Kochhar/Higgins