日常开发场景中,我们经常性会用到递归查询,oracle的递归查询用的是 start with connt by的方式,这个语式标识的 start with 表示开始查找的节点,connet by prior 表示关联条件。
话不多说,开始构造数据
正常来说我们需要找到ID=1下所有的节点,采取以下SQL:
SELECT * FROM TEST_IDPARENT START WITH ID=1 CONNECT BY PRIOR ID=PARENTID;
得到如下结果
如果我们需要排除某个子节点以及下属节点怎么办呢,例如我们需要排除长沙分部下的所有节点,这个时候,我们可以在CONNECT BY 后面加上排除条件,如下所属:
SELECT * FROM TEST_IDPARENT START WITH ID=1 CONNECT BY ID 3 AND PRIOR ID=PARENTID;
另外,有时候我们想知道我们的主节点是哪个,需要用到CONNECT_BY_ROOT用于获取主节点,参考如下:
SELECT CONNECT_BY_ROOT(NAME), T.*
FROM TEST_IDPARENT T
START WITH ID = 1
CONNECT BY ID 3
AND PRIOR ID = PARENTID;
还有我们常用到的,显示层级全路径以及级别需要用到 SYS_CONNECT_BY_PATH 以及LEVEL字段,其中,对于SYS_CONNECT_BY_PATH有2个参数,显示字段以及连接符
如下所示:
SELECT CONNECT_BY_ROOT(NAME), SYS_CONNECT_BY_PATH(NAME,'/'), LEVEL, T.*
FROM TEST_IDPARENT T
START WITH ID = 1
CONNECT BY PRIOR ID = PARENTID;
还有一个函数用于判断是否为叶子节点(即下属没有节点)CONNECT_BY_ISLEAF
SELECT CONNECT_BY_ROOT(NAME), SYS_CONNECT_BY_PATH(NAME, '/'), LEVEL, T.*
FROM TEST_IDPARENT T
WHERE CONNECT_BY_ISLEAF = 1
START WITH ID = 1
CONNECT BY PRIOR ID = PARENTID;
输出结果中,只有level=4级别的,因为LEVEL4级别下没有别的子节点了,所以CONNECT_BY_ISLEAF = 1过滤的是下属没有子节点的数据(即叶子节点)。