1、函数简介:
首先SYS_CONNECT_BY_PATH这个函数是oracle9i新提出的!
然后这个函数要和:START WITH(非必须) 和 CONNECT BY PRIOR(必须)联合使用;
START WITH 开始遍历的的节点
CONNECT BY PRIOR 父子关系的对应
LEVEL 是树的深度
函数有俩个参数(形成树的字段名,层级分隔符)
2、示例代码:
SELECT T.IDS,T.P_ID,SYS_CONNECT_BY_PATH(T.REF_ID,'->') AS "PATH",LEVEL
FROM ORGPOST_TREE T
START WITH T.P_ID='0'
CONNECT BY PRIOR T.IDS=T.P_ID
3、注意事项:
带WHERE条件的查询语句
错误写法:
SELECT T.IDS,T.P_ID,SYS_CONNECT_BY_PATH(T.REF_ID,'->') AS "PATH"
FROM ORGPOST_TREE T
WHERE T.ORG_ID='012010007' AND T.WFC_VERSION='1' --或导致sql执行异常
START WITH T.P_ID='0'
CONNECT BY PRIOR T.IDS=T.P_ID
正确写法:作为子查询