Oracle的递归查询

•数据展示

 

 •查询code为001001001的所有子节点

SELECT d.id,d.code
FROM pm_project_tasks d
START WITH d.id='000166a618b8da13'
CONNECT BY PRIOR d.id=d.parentid order by d.code

>结果为：

 

 •查询code为001001007的最顶层节点

SELECT id,
code,
CONNECT_BY_ROOT(id),
CONNECT_BY_ROOT(code)
FROM pm_project_tasks t
WHERE t.id = '000166cdcae9d2fe'
START WITH t.code = '001'
CONNECT BY PRIOR ID = t.parentid

>结果为：

 

•查询code为001开始的计划下的所有节点的层级和是否最叶子节点

--LEVEL:查询该计划下的层级
--CONNECT_BY_ISLEAF：查询节点是否是叶子节点，是则为1，不是则为0
1 SELECT id, code, t.parentid, LEVEL, CONNECT_BY_ISLEAF
FROM pm_project_tasks t
where t.project_info_id = '0001635dca95aa44e'
START WITH t.code = '001'
CONNECT BY PRIOR ID = t.parentid
ORDER BY code;

 >结果为：

 

 •查询所有节点的路径

SELECT ID, t.code, SUBSTR(SYS_CONNECT_BY_PATH(code, '->'), 3) NAME_PATH
FROM pm_project_tasks t
where t.project_info_id = '0001635dca95aa44e'
START WITH t.code = '001'
CONNECT BY PRIOR ID = t.parentid
order by t.code

  >结果为：