Oracle Tree Query Summary

Recently encountered a problem in the company's project, multi-level cascading navigation menu, although only a level three directory, but

nested

, the database table structure is as follows:

1 CREATE TABLE FLFL    2 (   3number notNULL,   4 MC NVARCHAR2 (),    5  Number ,    6  Number   7 )  

Very common table structure, including its own ID and parent ID, for this we are not unfamiliar, in the writing tree menu often see such data structure, but we generally write the tree menu will use the front end of the framework such as Ztree or TreeView, we just need to find all the data in the background, Return list to the front desk will automatically parse the tree structure according to the ID and Superid. But if these frameworks are not used, we need to assemble them in the background to return to the foreground for processing. Then how to assemble the tree structure in the background, such as to achieve the following functions:

This is the time to use the database tree Query method. The data structure returned by the tree query is to find out the nodes of all descendants under the root node in a way that nodes contain nodes.

Primary syntax for Oracle tree queries:select...start with ... prior .

We take the above FLFL table As an example to explain.

1. Find all top-level parent nodes (the longest generation) in the tree.

1 SELECT * from FLFL WHERE sjflid =0;

The parent ID of all root nodes in the above query is 0, which can be set when the data is inserted. The root node is identified with a parent ID of 0.

2. Find the immediate child node (all sons) of a node.

1 SELECT * from FLFL WHERE sjflid = 819459 ;

This SQL statement can detect all child nodes with a parent node of 819454.

3. Find all the immediate child nodes (all descendants) of a node.

1 SELECT * from FLFL START with ID = 819459 CONNECT by sjflid = PRIOR ID;

This looks for all of the immediate sub-class nodes under the node with ID 819459, including all the subordinate nodes of the child's generation and grandchildren.

4. Find the immediate parent node (father) of a node.

1 SELECT B.*fromJOINon=WHERE=6758 

This finds the immediate parent node of the node with ID 6758, which is associated with the same table.

5. Find all the immediate parent nodes (ancestors) of a node.

1 SELECT * from FLFL START with ID = 6758 CONNECT by PRIOR sjflid = ID;

Here is the ID 6758 of all the immediate parent node, for example is to find a person's father, grandfather and so on. But it is worth noting that the order of the results of this query is to list the subclass node and then list the parent class node, let's say it is a reverse.

There are two tree queries listed above, the 3rd statement and the 5th statement, the difference between the two statements is that the location of the prior keyword is different, so the query is determined in different ways. When sjflid = PRIOR ID, the database iterates over the same record as the ID based on the current ID, so the result of the query is to iterate over all of the subclass records, while the PRIOR id = sjflid, the Sjflid The database iterates over the current sjflid with the same ID as the current sjflid, so the result of the query is the result of all the parent classes.

6. Query the sibling node (brother) of one node.

1 Select A.*from    flfl a   WHEREEXISTS (Select *    from FLFL b   WHERE=and=6757);  

The query here is the node with ID 6757 is the same parent node, like a brother.

7. Query the node (Gypsy) that is sibling to one node (s).

1  withTmp as(SELECTA.*, LevelLev fromFLFL a START withA.sjflid is NULLCONNECT byA.sjflid=PRIOR a.id)SELECT *    fromTmpWHERELev=(SELECTLev fromTmpWHEREId= 819394)

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.

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

1  withTmp as(SELECTFlfl.*, LevelLev fromFLFL START withSjflid is NULLCONNECT bySjflid=PRIOR ID)SELECTB.*   fromTMP B, (SELECT *  fromTmpWHEREId= 7004  andLev= 2) AWHEREB.lev= 1  UNION  All SELECT *  fromTmpWHERESjflid=(SELECT DISTINCTX.id fromTMP x, tmp y, (SELECT *   fromTmpWHEREId= 7004  andLev> 2) ZWHEREY.id=Z.sjflid andX.id=Y.sjflid);

Here the query is divided into the following steps. First, like the 7th, the whole table is used to add the level of temporary tables, and secondly, according to the level to determine a few types, in the above cited example, 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.

9. Query the sibling node (family uncle) of the parent node of a node.

1  withTmp as  2(SELECTA.*, LevelLev3  fromFLFL a4START withA.sjflid is NULL  5CONNECT byA.sjflid=PRIOR a.id)6 SELECT *   7  fromtmp8 WHERELev=(SELECTLev9  fromtmpTen WHEREId= 819394)- 1  

You just have to make a level decision.

Basically, the common query is inside, and there are some uncommon ones. Among them, the content of the query is the basic information of the node, all the basic fields in the data table, but there are some special requirements in the tree query, which is to deal with the query data, including the tree path.

Add a concept that, for a database, the root node is not necessarily the top-level node designed in the database, and for the database, the root node is where start with starts.

Some of the specific tree-related requirements are listed below.

name to list the full path of the name.

1 start at the top:2 3 4 SELECTSys_connect_by_path (MC,'/')   5  fromFLFL6 WHEREId= 6498   7START withSjflid is NULL  8CONNECT bySjflid=PRIOR ID; 9 Ten start with the current node: One  A  - SELECTSys_connect_by_path (MC,'/')    -  fromFLFL theSTART withId= 6498    -CONNECT byPRIOR Sjflid=ID;

In the above example, the first SQL is traversed from the root node, and the second SQL is directly to find the current node, in terms of efficiency is already very diverse, more critical is that the first SQL can only select one node, and the second SQL is to traverse a tree.

The Sys_connect_by_path function starts at the start with the beginning of the traversal, and notes its traversal to the node, start with the beginning of the place is treated as the root node, the path will be traversed according to the delimiter in the function, a new string is formed, This feature is still very powerful.

List The root node of the current node.

1  SELECT  Connect_by_root MC, FLFL. *   2  from FLFL    3  with = 6498   4  by = ID;  

The Connect_by_root function is used before the column to record the contents of the root node of the current node.

lists whether the current node is a leaf.

1 SELECT  Connect_by_isleaf, FLFL. *   2  from FLFL    3  with  is NULL  4  by = PRIOR ID;  

The Connect_by_isleaf function is used to determine if the current node contains a subordinate node, and if it is included, it is not a leaf node, which returns 0, or 1 if it does not contain a subordinate node.

Oracle Tree Query Summary