Oracle Tree Operations (select.. Start with. Connect by. Prior

recursive queries in Oracle can be used by: Select, start with, connect by.. Prior The following describes the common ways in which tree queries are used in Oracle, with only one table involved.

I. Build a TABLE statement:

--Menu directory Structure table CREATE TABLE Tb_menu (ID number) NOT NULL,--primary Key ID title VARCHAR2 (50),--Title parent number (10)- -Parent ID)------------------insert into Tb_menu (ID, title, parent) VALUES (1, ' parent menu 1 ', null); INSERT into Tb_menu (ID, title, parent) VA  Lues (2, ' parent menu 2 ', null); INSERT into Tb_menu (ID, title, parent) VALUES (3, ' parent menu 3 ', null); INSERT into Tb_menu (ID, title, parent) VALUES (4, ' parent menu 4 ', null), insert into Tb_menu (ID, title, parent) VALUES (5, ' parent menu 5 ', null);--first-level menu insert into Tb_menu (ID, title , parent) VALUES (6, ' level menu 6 ', 1); INSERT into Tb_menu (ID, title, parent) VALUES (7, ' level menu 7 ', 1); INSERT into Tb_menu (ID, title, Parent) VALUES (8, ' level menu 8 ', 1); INSERT into Tb_menu (ID, title, parent) VALUES (9, ' level menu 9 ', 2); INSERT into Tb_menu (ID, title, PA  Rent) VALUES (10, ' One-level menu ', 2), insert into Tb_menu (ID, title, parent) VALUES (11, ' One-level menu one ', 2); INSERT into Tb_menu (ID, title, Parent) VALUES (12, ' Level menu ', 3); INSERT into Tb_menu (ID, title, parent) VALUES (13, ' Level menu ', 3); INSERT into Tb_menu (ID, titl E, parent) VALUES (14, ' first classSingle ', 3); INSERT into Tb_menu (ID, title, parent) VALUES (15, ' Level menu ', 4); INSERT into Tb_menu (ID, title, parent) VALUES (16, ' one  Level menu (4), insert into Tb_menu (ID, title, parent) VALUES (17, ' Level menu ', 4); INSERT into Tb_menu (ID, title, parent) VALUES (18, ' Level menu ', 5); INSERT into Tb_menu (ID, title, parent) VALUES (19, ' Level menu ', 5); INSERT into Tb_menu (ID, title, parent) VALUES (2  0, ' level menu 20 ', 5);--Two-level menu insert into Tb_menu (ID, title, parent) VALUES (21, ' Two-level menu ', 6); INSERT into Tb_menu (ID, title, parent) VALUES (22, ' Level two menu ', 6); INSERT into Tb_menu (ID, title, parent) VALUES (23, ' Two-level menu ', 7); INSERT into Tb_menu (ID, title, paren T) values (24, ' Level two menu ', 7); INSERT into Tb_menu (ID, title, parent) VALUES (25, ' Two-level menu ', 8); INSERT into Tb_menu (ID, title, par  ENT) VALUES (26, ' Level two menu ', 9); INSERT into Tb_menu (ID, title, parent) VALUES (27, ' Two-level menu '); INSERT into Tb_menu (ID, title, Parent) VALUES (28, ' Two-level menu ', one); INSERT into Tb_menu (ID, title, parent) VALUES (29, ' Two-level menu, '); INSERT into Tb_menu (ID, TI TLE, parent) VALUES (30, ' twoMenu (ID, title, parent) VALUES (31, ' Level two menu, '), insert into Tb_menu (ID, title, parent) VALUES (3 2, ' Level two menu ', "INSERT into Tb_menu (ID, title, parent) VALUES (33, ' Two-level menu '," +); INSERT into Tb_menu (ID, title, parent) Valu  Es (34, ' Two-level menu, ' + '); INSERT into Tb_menu (ID, title, parent) VALUES (35, ' Two-level menu, '); INSERT into Tb_menu (ID, title, parent) VALUES (36, ' Two-level menu, ' + '); INSERT into Tb_menu (ID, title, parent) VALUES (37, ' Two-level menu 37 ', 20);--Three-level menu insert into Tb_menu (ID, tit Le, parent) values (38, ' Level three menu ', +); INSERT into Tb_menu (ID, title, parent) VALUES (39, ' Three-level menu, '); INSERT into Tb_menu (ID , title, parent) VALUES (40, ' Three-level menu, Max '), insert into Tb_menu (ID, title, parent) VALUES (41, ' Three-level menu, '); INSERT INTO Tb_men U (ID, title, parent) VALUES (42, ' Three-level menu ', +); INSERT into Tb_menu (ID, title, parent) VALUES (43, ' Three-level menu ", +); INSERT INTO TB _menu (ID, title, parent) VALUES (44, ' Level three menu, '), insert into Tb_menu (ID, title, parent) VALUES (45, ' Three-level menu, '); Insert in To Tb_menu (ID, title, parentVALUES (46, ' Three-level menu, '), insert into Tb_menu (ID, title, parent) VALUES (47, ' Three-level menu, '); INSERT into Tb_menu (ID, title, PA Rent) VALUES (48, ' Level three menu, '), insert into Tb_menu (ID, title, parent) VALUES (49, ' Three-level menu '), insert into Tb_menu (ID, titl E, parent) VALUES (50, ' Level three menu, ' + '); commit; SELECT * from Tb_menu;

The parent field is the ancestor ID, and if it is a top-level parent, the parent is null

Two. Tree operations

From the most basic operation, we will gradually list the common operations in tree queries, and all the queried nodes are based on the family's generation.

1), find all top-level parent nodes in the tree (the largest generation), assuming the tree is a directory structure, the first operation always finds all top-level nodes, and then finds its subordinate nodes based on that node.

SELECT * from Tb_menu m where m.parent is null;

2), find the immediate child node (all sons) of a node. If you are looking for a subordinate sub-class node, it is not necessary to use a tree query.

SELECT * from Tb_menu m where m.parent=1;

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

SELECT * from Tb_menu m start with m.id=1 connect by M.parent=prior m.id;

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

4), find the immediate parent node (father) of a node. If you are looking for a node directly under the parent node, it is not necessary to use a tree-type query.

--C:child, P:parentselect c.id, C.title, P.id parent_id, P.title parent_titlefrom tb_menu C, Tb_menu pwhere c.parent=p. ID and c.id=6

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

SELECT * from Tb_menu m start with m.id=38 connect by prior m.parent=m.id;

Here is the ID 1 of all the immediate parent node, for example is to find a person's father, grandfather and so on. However, it is worth noting that the order of the results of this query is to list the child class nodes and then list the parent class nodes.

%%%%%%%%%%%%%%

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 the parent

 =  prior ID, the database iterates over the same record as the parent with the ID based on the current ID, so the result of the query is to iterate through all of the subclass records, and when prior parent = ID, the database

with The current parent iterates over the record of the same ID as the current parent, so the result of the query is all the parent class results.

%%%%%%%%%%%%%%

6), Query the sibling node (brother) of a node.

--M.parent=m2.parent the same father select * from Tb_menu m1where exists (SELECT * from     tb_menu m2     where m1.parent=m2.paren T and     m2.id=6)

7), query the node with one node sibling (Gypsy). If you set a Level field in a table, it would be easy to do such a query, at the same level as that node, listing the implementation when the field is not used!

With TMP as (      select a.*, Level leaf from                 tb_menu a                      start with a.parent are null           connect by a.parent = Prior a.id) SELECT * FROM                                 tmp                             where leaf = (select leaf from tmp where id = 50);

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), the sibling node that queries the parent node of a node (uncle and uncle).   

With TMP as (    select tb_menu.*, Level Lev from      Tb_menu-start with parent was     NULL connect by     parent = Prior ID) Select b.* from  tmp B, (SELECT * FROM                tmp               where id = Lev = 2) a WHERE B.lev = 1union Allselect *  From tmp where the parent = (SELECT DISTINCT x.id from                   tmp x,--grandfather                        tmp y,--Father                        (SELECT * FROM                           tmp                          where id = lev > 2) z--son                  

here the query is divided into the following steps.

First, like the 7th one, use the temporary table plus the 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.

9), Query the sibling node (family uncle) of the parent node of a node. This is actually the same as the 7th case.

With TMP as (      select a.*, Level leaf from                tb_menu a                       start with a.parent are null            connect by a.parent = Prior a.id) SELECT * FROM                                 tmp                              where leaf = (select leaf from tmp where id = 6)-1;

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, is the basic field in the data table, but there are some special in the tree query

requirements, the query data is processed, including the list of tree paths.

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.

10), name to list the full path of the name.
There are two common cases, one that is listed from the top level until the name (or other attribute) of the current node, and one that is listed from the current node until the name (or other attribute) of the top-level node

Lift Address For example: the domestic habit is from the province began, to the city, to the county, to the Neighborhood committee, and foreign habits just the opposite.

Start at the top:

Select Sys_connect_by_path (title, '/') from  tb_menu where id = "Start with parent" is a null connect by parent = Prior Id

start with the current node:

Select Sys_connect_by_path (title, '/') from  tb_menu start with id = + Connect by prior parent = ID;

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 walk out of 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.

11), lists the root node of the current node. as stated earlier, the root node is where start with begins.

Select Connect_by_root Title, tb_menu.* from  tb_menu start with id = + Connect by prior parent = ID;

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

12), lists whether the current node is a leaf. This is more common, especially in dynamic catalogs, where it is useful to find out if there are subordinate nodes in the content.

Select Connect_by_isleaf, tb_menu.* from Tb_menu start with the parent is a  null connect by parent = Prior ID;

The connect_by_isleaf function is used to determine whether the current node contains a subordinate node, and if it is included, the description is not a leaf node, where 0 is returned, and 1 if it does not contain a subordinate node.

Excerpt from: http://www.cnblogs.com/linjiqin/archive/2013/06/24/3152674.html

Oracle Tree Operations (select.. Start with. Connect by. Prior