Oracle recursive query parent-Child sibling node

1. Querying all descendant nodes under a node (including parent nodes at all levels)

1 // all descendant nodes with a query ID of 101, all levels of parent nodes, including 101 2 Select T.*from +='101'by  = Prior ID

2. Querying all descendant nodes under a node (does not include all levels of parent nodes)

1 SelectT.*2    fromsys_org T3  where  not exists(Select 1  fromsys_org swheres.parent_id=t.id)4Start withId= '101'5Connect byparent_id=Prior ID

3. Query all parent nodes of a node (all ancestor nodes)

1 Select T.*2from     sys_org t3with  =  '401000501'4by= ID

4. Query all sibling nodes of a node (brother)

1 Select *  from sys_org T 2 where exists (Select*fromwhere t.parent_id= and s.id=  '401000501')

5. Query all sibling nodes (family nodes) of a node, assuming the level field is not set

1  withTmp as(2       SelectT.*, LevelLeaf3        fromsys_org T4Start witht.parent_id= '0'     5Connect byt.parent_id=Prior t.id)6 Select *                               7        fromtmp8 whereLeaf=(SelectLeaf fromTmpwhereId= '401000501');

Here are two tricks, one using level to identify each node's levels in the table, and using the WITH syntax to simulate a temporary table with a level

6. Query the parent node and sibling node (uncles node) of a node

 withTmp as(    SelectT.*, LevelLev fromsys_org t start witht.parent_id= '0'Connect byt.parent_id=Prior t.id)SelectB.* fromTMP B, (Select *             fromtmpwhereId= '401000501'  andLev= '2') AwhereB.lev= '1' Union  All Select * fromtmpwhereparent_id=(Select distinctx.id fromTMP x,--GrandpaTMP y,--Father(Select *                       fromtmpwhereId= '401000501'  andLev> '2') Z--son                whereY.id=z.parent_id andX.id=Y.PARENT_ID); 

Here the query is divided into the following steps.
First, use a temporary table plus a level for all tables;
Second, there are several types that can be judged by level, and in the case of cited above, there are three cases:
(1) The current node is the top node, that is, the query out of the Lev value is 1, then it does not have a parent node, not considered.
(2) The current node is a level 2 node, the query out of the Lev value is 2, so long as the guarantee Lev Level 1 is the sibling node of its ancestor.
(3) The other situation is 3 and above, then it is necessary to select the superior node (grandfather), then to judge the grandfather's subordinate node is the parent node of the node is the sibling node.
Finally, the result set is formed by combining the results of the query with Union.

Oracle recursive query parent-Child sibling node