Common Methods of tree-type queries in Oracle

In Oracle, the most important common method of tree-based query in Oracle is the select... start with... connect by... prior syntax. Based on this syntax, we can list a table structure in the tree order. The following describes the common query methods for tree-type queries in Oracle and the Oracle feature functions related to tree queries that are frequently used, here, only the tree query method in one table is involved, and the association among multiple tables is not involved. Take a table in a project I have done as an example. The table structure is as follows: SQL code 1. create table flfl 2. (3. id number not null, 4. MC NVARCHAR2 (20), 5. fljb number, 6. sjflid number 7 .) www.2cto.com FLJB is the tree level, which can accelerate the SQL query efficiency in many queries. The function shown below basically does not use this keyword. SJFLID stores the parent ID. If it is a top-level parent node, the SJFLID is null (you must add that it was designed in the past, but now we know that it is better to have a null record in the table, this will cause full-text scanning. It is recommended to replace it with 0 ). We gradually list common operations in tree queries from the most basic operations, so the queried nodes are represented by generations in the family. 1. Find all top-level parent nodes in the tree (the longest generation ). Assuming that the tree is a directory structure, the first operation always finds all the top-level nodes and then finds their subordinate Nodes Based on the node. SQL code www.2cto.com 1. SELECT * FROM flfl WHERE sjflid IS NULL; this IS an introduction and tree query IS not used. 2. Find the subnode directly under a node (all sons ). Tree-based query is not required if you are looking for directly-affiliated subclass nodes. SQL code 1. SELECT * FROM flfl WHERE sjflid = 819459; this can find the directly-affiliated subclass node with ID 819459. 3. Search for all directly affiliated subnodes (all descendants) of a node ). SQL code 1. SELECT * FROM flfl start with id = 819459 connect by sjflid = prior id; this is used to find all the directly-affiliated sub-class nodes under the node with id 819459, including all subordinate nodes of the child and the child. Www.2cto.com 4. Find the parent node (parent) of a node ). If you are looking for the immediate parent node of a node, tree-based query is not required. SQL code 1. SELECT B. * FROM flfl a JOIN flfl B ON. sjflid = B. id where. ID = 6758; this is the immediate parent node of the node whose ID is 6758. The Association of the same table is required. 5. Search for all parent nodes (ancestor) of a node ). SQL code 1. SELECT * FROM flfl start with id = 6758 connect by prior sjflid = ID; all directly affiliated parent nodes with id 6758 are searched here, for example, finding a person's father or grandfather. However, it is worth noting that the order of the query results is to first list child-class nodes and then list parent-class nodes. This is an inverted order. On www.2cto.com, two tree-based query methods are listed, with 3rd and 5th statements. The difference between the two statements is that the prior keyword is located differently, so the query method is different. When sjflid = prior id, the database iterates records with the same sjflid and ID based on the current ID, so the query result is that all subclass records are iterated; when prior id = sjflid, the database iterates records with the same id as the current sjflid according to the current sjflid. Therefore, the query result is the result of all parent classes. The following are a series of deeper queries targeting the tree structure. The queries here are not necessarily the optimal query method, but may be just one of them. 6. query the sibling nodes (brothers) of a node ). SQL code 1. SELECT. * 2. FROM flfl a 3. where exists (SELECT * 4. FROM flfl B 5. WHERE. sjflid = B. sjflid AND B. ID = 6757); here, the query is the same as the node with ID 6757, it is like a brother. 7. query nodes (sibling) at the same level as a node ). If a level field is set in the table and FLJB is set in the table above, it is easy to perform such queries. The level is the same as that of the node, the implementation when this field is not used is listed here! SQL code www.2cto.com 1. WITH tmp AS 2. (SELECT. *, LEVEL lev3. FROM flfl a 4. start with. sjflid is null 5. connect by. sjflid = PRIOR. ID) 6. SELECT * 7. FROM tmp 8. WHERE column = (SELECT column 9. FROM tmp 10. where id = 819394) Here we use two tips: one is to use LEVEL to identify the LEVEL of each node in the table, and the other is to use the with syntax to simulate a temporary table with a LEVEL. 8. query the sibling nodes (uncle and Uncle) of the parent node of a node ). SQL code 1. WITH tmp AS 2. (SELECT flfl. *, LEVEL lev3. FROM flfl 4. start with sjflid is null 5. connect by sjflid = prior id) 6. SELECT B. * www.2cto.com 7. FROM tmp B, 8. (SELECT * 9. FROM tmp 10. where id = 7004 AND lev= 2) a 11. WHERE B. ev = 1 12. union all 13. SELECT * 14. FROM tmp 15. WHERE sjflid = (select distinct x. ID 16. FROM tmp x, 17. tmp y, 18. (SELECT * 19. FROM tmp 20. where id = 700. 4 AND lev> 2) z 21. WHERE y. ID = z. sjflid AND x. ID = y. sjflid); the query here is divided into the following steps. First, add the 7th same tables to the temporary tables. Second, judge whether there are several types based on the level. For the example above, there are three situations: (1) if the current node is a top-level node, that is, the query results show that the column value is 1, so it does not have a parent node and is not considered. (2) If the current node is a Level 2 node and the queried column value is 2, you only need to ensure that the column Level 1 is the brother node of the parent node. (3) In other cases, the upper-level nodes (grandfathers) of the superiors must be selected ), then, we can determine that all the lower-level nodes of the grandfather are brother nodes belonging to the upper-level nodes of the node. Finally, UNION is used to combine the query results to form a result set. 9. query the peer node (Family uncle) of the parent node of a node ). This is actually the same as in 7th cases. SQL code 1. WITH tmp AS 2. (SELECT. *, LEVEL lev3. FROM flfl a 4. start with. sjflid is null 5. connect by. sjflid = PRIOR. ID) 6. SELECT * www.2cto.com 7. FROM tmp 8. WHERE column = (SELECT column 9. FROM tmp 10. where id = 819394)-1 only requires a level judgment. Basically, common queries are included, and some of them are not common. The query content is the basic information of the node and the basic fields in the data table, but there are some special requirements in the tree query, which process the query data, common examples include listing tree paths. In addition, for a database, the root node is not necessarily the top-level node designed in the database. For the database, the root node is the place where start with starts. The following lists some special requirements related to trees. 10. The name must list all the paths of the name. There are two common cases: list from top level until the name of the current node (or other attributes), and list from the current node, until the name (or other attributes) of the top-level node ). Take the address as an example: the habits in China are from the province to the city, to the County, to the neighborhood committee, while those in foreign countries are the opposite (the teacher said that he hasn't received emails from outside China, ). Start from the top: www.2cto.com SQL code 1. SELECT SYS_CONNECT_BY_PATH (mc, '/') 2. FROM flfl 3. where id = 6498 4. start with sjflid is null 5. connect by sjflid = prior id; from the current node: SQL code 1. SELECT SYS_CONNECT_BY_PATH (mc, '/') 2. FROM flfl 3. start with id = 6498 4. connect by prior sjflid = ID; here I have to complain again. Oracle only provides one sys_connect_by_path function, but forgets the order of string connections. In the above example, the first SQL statement is to traverse from the root node, while the second SQL statement is to directly find the current node, which is already very different in terms of efficiency, more importantly, the first SQL statement can only select one node, while the second SQL statement traverses a tree. PS again. The function www.2cto.com sys_connect_by_path is used to traverse the node starting from start with, and write down the node to which it traverses. The Node starting from start with is regarded as the root node, the traversal path is a new string based on the Separator in the function. This function is very powerful. 11. List the root nodes of the current node. As mentioned earlier, the root node is where start with starts. SQL code 1. SELECT CONNECT_BY_ROOT mc, flfl. * 2. FROM flfl 3. start with id = 6498 4. connect by prior sjflid = ID; The connect_by_root function is used to record the content of the root node of the current node. 12. List whether the current node is a leaf. Www.2cto.com is a common function, especially in dynamic directories. This function is useful when detecting whether there are subnodes in the content. SQL code 1. SELECT CONNECT_BY_ISLEAF, flfl. * 2. FROM flfl 3. start with sjflid is null 4. connect by sjflid = prior id; The connect_by_isleaf function is used to determine whether the current node contains a lower-level node. if it contains a lower-level node, it indicates that it is not a leaf node, and 0 is returned. If it does not contain a lower-level node, 1 is returned here.