Shulou(Shulou.com)06/01 Report--

Preface

When it comes to the recursive query syntax in Oracle, I think there are some basic database shoes that should be known, and should also be used when doing a project. The following article will introduce the relevant content of Oracle through recursive query father and son sibling nodes, and share them for your reference and study.

The methods are as follows:

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

/ / query all descendant nodes whose id is 101, including all levels of parent node select t.* from SYS_ORG t start with id = '101' connect by parent_id = prior id

2. Query all descendant nodes under a node (excluding parent nodes at all levels)

Select t.* from SYS_ORG t where not exists (select 1 from SYS_ORG s where s.parent_id = t.id) start with id = '101'connect by parent_id = prior id

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

Select t.* from SYS_ORG t start with id = '401000501' connect by prior parent_id = id

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

Select * from SYS_ORG t where exists (select * from SYS_ORG s where t.parent_id=s.parent_id and s. Idyllic 401000501')

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

With tmp as (select. Connect by t.parent_id, level leaf from SYS_ORG t start with t.parent_id ='0' connect by t.parent_id = prior t.id) select * from tmp where leaf = (select leaf from tmp where id = '401000501')

Two techniques are used here, one is to use level to identify the level of each node in the table, and the other is to use with syntax to simulate a temporary table with a level.

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

With tmp as (select. And lev, level lev from SYS_ORG t start with t.parent_id ='0' connect by t.parent_id = prior t.id) select b.*from tmp b, (select * from tmpwhere id = '401000501' and lev =' 2') awhere b.lev = '1'union allselect * from tmpwhere parent_id = (select distinct x.id from tmp x,-- grandfather tmp y) -- father (select * from tmp where id = '401000501' and lev >' 2') z-- son where y.id = z.parent_id and x.id = y.parent_id)

The query here is divided into the following steps.

First, use temporary tables to add levels to all tables

Secondly, there are several types according to the level. For the example given above, there are three cases:

(1) if the current node is a top-level node, that is, if the value of leve queried is 1, then it has no parent node and will not be considered.

(2) the current node is a level 2 node, and the queried levo value is 2, so as long as you ensure that the lev level 1 is the sibling node of its parent node.

(3) in other cases, if the level is 3 or above, then the superior node (grandfather) of its superior should be selected, and then the grandfather's subordinate node should be judged to be the sibling node of the superior node of that node.

Finally, union is used to combine the query results to form a result set.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.