An amazing use of Oracle hierarchical Query

Query all nodes and indent them by node.

Select * from organ connect by parent_id = prior ID start with parent_id is null;

The working method is to first find the record whose parent_id is null, and then expand it layer by layer according to the rules specified by parent_id = prior ID.

Here, prior indicates that the ID of the previous layer is equal to the parent_id extension of the current layer, which is equivalent to connect by this_row.parent_id = prior_row.id.

You can also use combination conditions in the connect by clause. In addition, if the conditions in connect by do not have prior, it is considered a simple pruning condition.

This usage is not new. today, we found a wonderful use. We can find all its parent Nodes Based on a node. This can be used to determine whether the child node belongs to the parent node. In this case, we can still use this hierarchical query. The query method is as follows:

Select * from organ connect by prior parent_id = ID start with ID = 861;

In this way, all upper-level nodes can be traced layer by layer.