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

The hierarchical relationship of tree structure is the relationship between father and son, which is similar to the departmental organizational structure tree in a company.

Among them, the general manager is the largest parent node, and there are assistant, marketing department, administrative office, R & D department and other sub-nodes, while the R & D department is subdivided into R & D department 1 and R & D 2.

Such as employee_id and manager_id in the hr user table employees. Employee_id represents the number of the employee, and manager_id represents the number of the person leading the employee, that is, the manager_ id value of the child node is equal to the employee_ id value of the parent node. There is a manager_id in each row of the table that represents the parent node (except the root node), and the entire tree structure can be determined by the parent node of each node.

Use the CONNECT BY nice START WITH clause in the SELECT command to query the tree structure relationships in the table. The format of the command is as follows:

SELECT .

CONNECT BY {PRIOR column name 1 = column name 2 | column name 1=PRIOR split name 2}

[START WITH]

Where the CONNECT BY clause states that each row of data will be retrieved in a hierarchical order and specifies that the data in the table will be connected to the tree structure. The PRIORY operator must be placed before either of the two columns of the join relationship. For the parent-child relationship between nodes, the PRIOR operator represents the parent node on one side and the child node on the other, thus determining whether the search tree structure is in the order of top-down or bottom-up. In a join relationship, column expressions are allowed in addition to column names.

The START WITH clause is optional and is used to identify which node is the root (start) node of the lookup tree. If the clause is omitted, it means that all rows that meet the query criteria are the root node.

START WITH: you can specify not only one root node, but also multiple root nodes.

Before learning, let's first have a conceptual understanding and understanding of the keywords that will be used in the tree structure chapter!

Prior: priority

STARTWITH: specify the starting point of the traversal

CONNECT BY PRIOR: specify the order of traversal, such as top to bottom, bottom to top

CONNECT BY NOCYCLE PRIOR: no loop traversal, which can avoid the occurrence of an endless loop

SYS_CONNECT_BY_PRTH: function that specifies the hierarchical delimiter

CONNECT_BY_ISLEAF: function, which returns values of 0 and 1. 0 represents a non-last node, that is, there are children below; 1 represents the last node, that is, there are no more child nodes below

According to the keywords above, let's combine a sql statement: show the affiliation of the users in the employees table, similar to the general manager / deputy general manager / administrative office director / clerk.

We use the START WITH statement to specify the source of the traversal, that is, where to start, START WITH can be omitted, if omitted, it means that all rows that meet the query criteria are the root node.

CONNECT BY PRIOR is used to specify the traversal order, and the two ends of the equal sign connect the father-son relationship, that is, whether to start looking for a son or a son.

E.employee_id=e.manager_id means: first find the employee's information, and then go to the superior, which belongs to this employee, that is, from top to bottom.

About the need for LEVEL:LEVEL to be used with CONNECT BY to indicate the hierarchy of the tree structure, LEVEL is the reserved keyword for Oracle

Example 1: the following sql statement is used for employee traversal display from top to bottom, and the hierarchy is used / segmented

SQL > SELECTLPAD ('', 2 * LEVEL-1) | | SYS_CONNECT_BY_PATH (e.last_name,'/') AS path

FROMemployees e

STARTWITH e.manager_id IS NULL

CONNECT BY PRIORe.employee_id = e.manager_id

Example 2: find information about all employees led by FORD and BLAKE.

SQL > SELECT EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME IN ('FORD','BLAKE')

EMPNO ENAME MGR

-

7698 BLAKE 7839

7499 ALLEN 7698

7521 WARD 7698

7654 MARTIN 7698

Example 3: the tree structure result set does not contain the tree relationship of Hartstein, which we can implement in this way

SQL > SELECTLPAD ('', 2 * LEVEL-1) | | SYS_CONNECT_BY_PATH (e.last_name,'/') AS path

FROM employees e

START WITHe.manager_id IS NULL

CONNECT BY PRIORe.employee_id = e.manager_id

AND e.last_name 'Hartstein'

Description: conditional filtering by adding the AND keyword after the CONNECT BY clause

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.