【Oracle】树查询(select...start with... connect by ...prior/connect_by_isleaf)

1.表数据：UP_COMPLAINT_CODE为该数据的父节点id

2.构建树形结构查询

SELECT LPAD (' ', LEVEL * 3, ' ') || COMPLAINT_NAME,
           COMPLAINT_CODE,
           UP_COMPLAINT_CODE
      FROM tablename
START WITH UP_COMPLAINT_CODE IS NULL
CONNECT BY PRIOR COMPLAINT_CODE = UP_COMPLAINT_CODE

查询结果：

start with 父节点ID=’…’ connect by prior 子节点ID = 父节点ID

以父节点 IS NULL为主节点，查询所有自己的后代节点
Tips：加 prior 表示包括自己，前后都可以加

3.判断是否叶子节点

SELECT LPAD (' ', LEVEL * 3, ' ') || COMPLAINT_NAME,
           COMPLAINT_CODE,
           UP_COMPLAINT_CODE,
           CONNECT_BY_ISLEAF AS 是否是叶子节点
      FROM tablename
START WITH UP_COMPLAINT_CODE IS NULL
CONNECT BY prior COMPLAINT_CODE =  UP_COMPLAINT_CODE

查询结果：

CONNECT_BY_ISLEAF 表示是否叶子节点，0表示否，1表示是

4.判断是否存在循环节点

循环节点： B的父节点是A，C的父节点是B，A的父节点是C

SELECT LPAD (' ', LEVEL * 3, ' ') || COMPLAINT_NAME,
           COMPLAINT_CODE,
           UP_COMPLAINT_CODE,
           connect_by_iscycle AS 是否存在循环引用节点
      FROM tablename
START WITH UP_COMPLAINT_CODE IS  NULL
CONNECT BY nocycle prior COMPLAINT_CODE =  UP_COMPLAINT_CODE

注意：connect_by_iscycle 必须与 nocycle 一起用