Oracle Tiered queries

1. When a parent-child relationship exists between rows in a table, the parent-child relationship between rows and rows can be queried by using Connect by query

SelectLpad ('-',( Level-1),'-')||Empno asTempno, Ename,mgr, Level, Decode ( Level,1,1) asRoot, Decode (Connect_by_isleaf,1,1) asLeafnode fromEmpstart withEmpno=7566Connect by(Prior empno)=MgrOrder  by  Level;

In addition to the level pseudo-column, Connect_by_isleaf is also a pseudo-column, 1 means No child records, and 0 is the opposite

2. When one of the attributes in a table needs to be stitched together according to a parent-child relationship, you can use Sys_connect_by_path

 withX1 as   (SelectDeptno,ename,row_number () Over(Partition byDeptnoOrder  byENAME) asRn fromemp1)SelectDeptno,sys_connect_by_path (ename,',') asNamec fromX1whereConnect_by_isleaf=1Start withRn=1Connect by(Prior Deptno)=Deptno and(Prior RN)=Rn-1;

3. For tree query results ordering, it is critical to keep the tree structure correct, so we use the Siblings keyword to sort the branches internally only

SelectLpad ('-',( Level-1),'-')||Empno asTempno, Ename,mgr, Level, Decode ( Level,1,1) asRoot, Decode (Connect_by_isleaf,1,1) asLeafnode fromEmp1start withEmpno=7566Connect by(Prior empno)=MgrOrderSiblings byEmpnodesc;

For a non-parent-child relationship field, you need to make a subquery for the filter condition of the field, and then make a tree query based on that subquery.

4. The above several queries are from the root to the child node query, how to recursively query from the child node to the root, as shown below

Select ename,mgr,level from with   empno=7902  by=  empnoorderby level;

Oracle Tiered queries