08.SQL Basic--Hierarchical query (START by ... CONNECT by PRIOR)

SQL Basics--Hierarchical query (START by ...) CONNECT by PRIOR)

Hierarchical query, or tree structure query, is one of the frequently used functions in SQL, which is usually composed of root node, parent node, child node, leaf node, and its syntax is as follows:

   SELECT [level], column,expression,...   From table_name   [WHERE where_clause]   [[START with Start_condition] [CONNECT by PRIOR Prior_condition];     Level: is a pseudo-column that represents the hierarchical start_condition of a tree   : The starting condition for a hierarchical query

--Using Start with ... connect by prior from the root node

Select Empno,mgr,ename,job from Empstart with empno = 7839connect by prior empno = Mgr;

The tree-structured traversal process (described by the query above) 1). Start with the root node (that is, the condition in Where_clause, if the root node is a non-root node, start the traversal as the root node, as in the example empno = 7839) 2). Traverse the root node (get empno = 7839 Records) 3). Determine if the node exists in child nodes, if you access the leftmost sub-node that is not accessed, go to), otherwise the next step in the example above is prior_condition = Empno = Mgr, that is, the MGR of the child node equals the empno of the parent node, At this time Mgr is 7839 record 4). When the node is a leaf node, the access is complete, otherwise, go to) 5). Return to the parent node of the node, go to the use of pseudo-column level-note connect by prior empno = Mgr Understanding--prior represents the previous record , that is, the MGR of the next return record should be equal to the empno of the previous record

　　

Select Level,empno,mgr,ename,job from Empstart with ename = ' KING ' connect by prior empno = Mgrorder by level;

--Get the number of layers

Select COUNT (Distinct level) "level" from empstart with ename = ' KING ' connect by prior empno = Mgr;

--Formatting hierarchical query results (using left padding * level-1 spaces)

 for A30 Select Level ,  lpad (',21"ename",   ' KING '

--Start the traversal from the non-root node (just modify the conditions in start with)

Select level,  lpad (", 2 * level-1) | | ename as" ename ",  jobfrom empstart with ename = ' SCOTT ' Connect by prior E Mpno = Mgr;

--traverse from the bottom up (swapping the conditions in connect by prior, using MGR = empno)--note that connect by prior MGR = Empno Understanding--prior represents the previous record, That is, the empno of the next return record should be equal to the MGR of the previous record.

Select level,  lpad (", 2 * level-1) | | ename as" ename ",  

--traverse from bottom up (you can also put prior to the right of the equals sign to get the same result)

Select level,  lpad (", 2 * level-1) | | ename as" ename ",  jobfrom empstart with ename = ' SCOTT ' Connect by empno = Prior Mgr;

--delete nodes and branches from the hierarchical query

Select level,  lpad (", 2 * level-1) | | ename as" ename "  , Jobfrom empwhere ename! = ' SCOTT '  – filtering SCO through the WHERE clause TT user, but Scott's subordinate Adams did not filter out start with empno = 7839   Connect by prior empno = Mgr;

--Filter out Scott and its subordinates by moving the filter condition from the contents of the WHERE clause into the CONNECT by prior clause

Select Level ,  lpad (',21"ename"    7839  'SCOTT';

--Add filters or use subqueries in a hierarchical query

Select level,  lpad (", 2 * level-1) | | ename as" ename "  , Jobfrom empwhere sal > 2500start with empno = 7839co Nnect by prior empno = Mgr;

Select level,  lpad (", 2 * level-1) | | ename as" ename "  , Jobfrom empwhere sal > (select AVG (SAL) from EMP) STA RT with empno = 7839connect by prior empno = Mgr;

08.SQL Basic--Hierarchical query (START by ... CONNECT by PRIOR)