•数据展示
•查询code为001001001的所有子节点
1 SELECT d.id,d.code 2 FROM pm_project_tasks d 3 START WITH d.id=‘000166a618b8da13‘ 4 CONNECT BY PRIOR d.id=d.parentid order by d.code
>结果为：
•查询code为001001007的最顶层节点
1 SELECT id, 2 code, 3 CONNECT_BY_ROOT(id), 4 CONNECT_BY_ROOT(code) 5 FROM pm_project_tasks t 6 WHERE t.id = ‘000166cdcae9d2fe‘ 7 START WITH t.code = ‘001‘ 8 CONNECT BY PRIOR ID = t.parentid
>结果为：
•查询code为001开始的计划下的所有节点的层级和是否最叶子节点
--LEVEL:查询该计划下的层级
--CONNECT_BY_ISLEAF：查询节点是否是叶子节点，是则为1，不是则为0
1 SELECT id, code, t.parentid, LEVEL, CONNECT_BY_ISLEAF 2 FROM pm_project_tasks t 3 where t.project_info_id = ‘0001635dca95aa44e‘ 4 START WITH t.code = ‘001‘ 5 CONNECT BY PRIOR ID = t.parentid 6 ORDER BY code;
>结果为：
•查询所有节点的路径
1 SELECT ID, t.code, SUBSTR(SYS_CONNECT_BY_PATH(code, ‘->‘), 3) NAME_PATH 2 FROM pm_project_tasks t 3 where t.project_info_id = ‘0001635dca95aa44e‘ 4 START WITH t.code = ‘001‘ 5 CONNECT BY PRIOR ID = t.parentid 6 order by t.code
>结果为：