根据父节点id查出所有子节点--树结构:
SELECT a.*
FROM SYS_DEPT a
WHERE NOT EXISTS (SELECT * FROM SYS_DEPT b WHERE b.pdept_id = a.dept_id)
START WITH pdept_id = '3C2D25A1-7046-43F5-A73D-DCF675E4EAB0' CONNECT BY PRIOR dept_id=a.pdept_id;
根据子节点ID反向追溯到根节点---树结构:
select * from SYS_DEPT start with dept_id='DBEC7D99-73E0-4EA8-9022-26A47B85FAD2' connect by prior pdept_id=dept_id
通过根节点遍历子节点:
select * from SYS_DEPT start with pdept_id='ABDA3ED8-CB84-4FF1-A1A5-5B652E9DF03E' connect by prior dept_id=pdept_id
通过level关键字可以知道每个节点的层级:
select a.*,level from SYS_DEPT a start with pdept_id='ABDA3ED8-CB84-4FF1-A1A5-5B652E9DF03E' connect by prior dept_id=pdept_id