ORACLE提供了一种树形结构用来实现层次查询:
START WITH :指定记录是根节点的条件。根节点可以有多个。
CONNECT BY :指定记录之间是父节点和子节点关系的条件。查找出含有子节点的父节点的条件
PRIOR :指定父节点记录的引用。
SIBLINGS :指定树的排序。同一父节点下的兄弟节点的顺序。
----------------------------------------------------------------------------------------------------
select * from XRH_ORGANISE
start with PARNET_ID = -1
connect by prior "ID" = PARNET_ID
-----------------------------------------------------------------------------------------------------
SELECT * FROM XRH_ORGANISE
WHERE DELETE_STATUS <> 0
START WITH PARNET_ID = -1 AND DELETE_STATUS <> 0
CONNECT BY PRIOR "ID" = PARNET_ID AND DELETE_STATUS <>0
ORDER SIBLINGS BY ORDER_NO
#DELETE_STATUS是删除状态 (假删除)0已删除 1未删除