Oracle--connect by, level, Start with (hierarchical query-hierarchy query)

Find the leader with employee number 7369:

1 SELECT level,e.* from EMP E CONNECT by PRIOR e.mgr = e.empno  78762 order by level
DESC

' Start with '--this identifies all level=1 nodes

"Connect by"--describes you to walk from the parent nodes above to their children and
their childrens children.

Easiest to use a example on EMP. If we start with "where Mgr is NULL", we generate the
Set of employees that have no MGR (they are the "top" tree). If we

CONNECT by PRIOR EMPNO =/* Current */MGR

That'll take all of the PRIOR records (the "Start with" at a) and find all records
such that's MGR column equals their EMPNO (find all the records of people managed by
The people we started with).

Optimizing query results with the WITH statement: optimizing levels

1 with A as
 2  1 LVL
 3 to     EMP E
 4   CONNECT by PRIOR e.mgr = e.empno
 5    7876
   
 6 Order    by level DESC
 7 SELECT a.lvl highest level plus 1, 8 level        current level,
 9        A.lvl-level optimized grade ,        e.* from A, one 7876 order by level         DESC

Find all subordinates with employee number 7839 (7839 is king):

1 SELECT level rank, e.*
2 from   EMP E
3 CONNECT by PRIOR e.empno = e.mgr
4  7839

--Structure the entire hierarchy

1 Select Lpad (', level*2, ') | | Ename ename, empno, Mgr
2 from     emp
3     START with Mgr are NULL
4     CONNECT by PRIOR empno = Mgr

So, the "KING is" the start with set then JONES BLAKE and CLARK fall under him. Each of them
becomes the PRIOR record in turn and their trees are.

Use connect by combining level to construct virtual rows:

5

Use RowNum to implement similar functions:

5

---------------------to Be Continued-----------------------

Constructs a two-level node tree using UNION ALL:

The view looks like this:

1 CREATE OR REPLACE VIEW Tree_view as
 2 SELECT
 3  ' 1 ' as Rootnodeid,
 4  ' xxxx Limited liability company ' as Treename,
 5  '-1 '  As parent_id
 6 from dual
 7 UNION
 8 SELECT
 9   to_char (d.deptno),   D.dname | | '_' || D.loc,
one   ' 1 ' as parent_id
from  dept D;

Query statement:

1 SELECT t.*, Level
2   tree_view T
3  START with t.parent_id = '-1 '
4 CONNECT by PRIOR T.rootnodeid = t.parent_id

-----The following updates:

1, first view a total of several levels:

1 SELECT COUNT (level)
2 out of   EMP E
3 CONNECT by PRIOR e.empno = e.mgr
4 START and E.mgr is  NULL;

2, view the number of each level. Mainly through the level of group by

1 SELECT COUNT (level)
2 out of   EMP E
3 CONNECT by PRIOR e.empno = e.mgr
4 START and e.mgr is  null< C15/>5  GROUP by level;

3, Oracle 10g provides a simple connect_by_isleaf=1,

0 means non-leaf node

1 SELECT level as rank, connect_by_isleaf as is a leaf node, e.*
2 from   EMP E
3 CONNECT by PRIOR e.empno = E.mgr
  4  START with E.mgr is NULL

4, Sys_connect_by_path

Oracle 9i provides Sys_connect_by_path (Column,char), where column is character type or can be automatically transferred

The name of the column to be converted to character type. Its primary purpose is to present the "path" of the parent node to the current node in the specified pattern. This function can only be used in hierarchical queries.

1 SELECT level as rank,
2        connect_by_isleaf as is leaf node,
3        21) | | Sys_connect_by_path (ename, ' => ')
4 from   EMP E
5 CONNECT by PRIOR e.empno = e.mgr
6  START with E.M GR is NULL;

5. Trim branches and nodes: