Oracle Connect by Tree query three (super verbose)

Find leaders with employee number 7369:

1 SELECT level,e.* from EMP E CONNECT by PRIOR e.mgr = E.empno  START with e.empno = 7876
2 ORDER by Level DESC

' Start with '--this identifies all level=1 nodes in the tree

"Connect By" – describes how 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 has no MGR (they is the top of the tree). If we

CONNECT by PRIOR EMPNO =/* Current */MGR

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

Using the WITH statement to refine query results: Optimizing levels

1 with A as
2  (SELECT MAX (level) + 1 LVL
3 from     EMP E
4   CONNECT by PRIOR e.mgr = E.empno
5    START with e.empno = 7876
6    ORDER by Level DESC)
7 SELECT a.lvl highest level plus 1,
8        levels current level,
9        A.lvl-level optimized post level,
Ten        e.* from A,
        EMP E CONNECT by PRIOR e.mgr = E.empno START with e.empno = 7876 ORDER by Level DESC

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

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

--Structure the entire hierarchy

1 Select Lpad (", Level*2,") | | Ename ename, Empno, Mgr
2 from     EMP
3     START with MGR is NULL
4     CONNECT by PRIOR EMPNO = MGR

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

To construct a virtual row using connect by with level:

1 SELECT level from DUAL CONNECT by level < 5

Use RowNum to achieve similar functionality:

1 SELECT level from DUAL CONNECT by level < 5

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

Use union ALL to construct a tree of two-tier nodes:

The views are as follows:

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),
Ten   D.dname | | ' _ ' | | D.loc,
One   ' 1 ' as parent_id
from  dept D;

Query statement:

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

-----The following is the updated content:

1, first look at a total of several levels:

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

2. Check the number of people at each level. The group by is primarily through level

1 SELECT COUNT (level)  2 from   EMP E  3 CONNECT by PRIOR e.empno = e.mgr  4  START with E.mgr is NULL
   5  GROUP by level;

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

0 indicates non-leaf nodes

1 SELECT level as class, connect_by_isleaf as is 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 a character type or can be automatically turned

Replace the column name with the character type. Its main purpose is to show the "path" of the parent node to the current node according to the specified pattern. This function can only be used in hierarchical queries.

1 SELECT level as class,  2        connect_by_isleaf as is leaf node,  3        lpad (', Level * 2-1) | | Sys_connect_by_path (ename, ' = = ')  4 from   EMP E  5 CONNECT by PRIOR e.empno = e.mgr  6 START with  e.mg R is NULL;

5. Pruning Branches and nodes:

Filter out the number is 7566 of the data (pruning node), he refers to the node to be cut off, but does not destroy the tree structure, its child nodes can still be normal display.

1 SELECT level as class,  2        connect_by_isleaf as is leaf node,  3        lpad (', Level * 2-1) | | Sys_connect_by_path (ename, ' = = '),  4        e.* 5 from   EMP E  6 WHERE e.empno! = 7566  7 CONNECT by PRIOR e.empno = e.mgr  8  START with E.mgr is NULL;

The cropped number is a node of 7698 and its child nodes:

1 SELECT level as class,  2        connect_by_isleaf as is leaf node,  3        lpad (', Level * 2-1) | | Sys_connect_by_path (ename, ' = = '),  4        e.* 5 from   EMP E  6 CONNECT by PRIOR e.empno = E.mgr  7< C10/>and e.empno! = 7698  8  START with E.mgr is NULL;

6, the use of Connect_by_root, oracle10g new connect_by_root, used before the column name represents the value of the same column name of the root node of this row.

1 SELECT level as class,  2        connect_by_isleaf as is a leaf node,  3        connect_by_root ename,  4        lpad (' ', Level * 2-1) | | Sys_connect_by_path (ename, ' = = '),  5        e.* 6 from   EMP E  7 CONNECT by PRIOR e.empno = E.mgr  8  START with E.mgr is NULL;

For hierarchical queries, if ordered by order, such as order by last_name, the level is first done, and then sorted by last_name, which destroys the hierarchy, such as a particular focus on the depth of a row, sorted by level, and will destroy levels. In oracle10g, the ordering of the siblings keyword is increased.

Syntax: Order siblings by <expre>

It will protect the hierarchy and sort by expre in each level.

1 SELECT level as class,  2        connect_by_isleaf as is leaf node,  3        lpad (', Level * 2-1) | | Sys_connect_by_path (ename, ' = = '),  4        e.* 5 from   EMP E  6 CONNECT by PRIOR e.empno = E.mgr   7< C10/>start with E.mgr are NULL  8  ORDER siblings by  E.ename;

Connect_by_iscycle (There is a loop, will return 1, otherwise return 0)

The connect_by_iscycle Pseudocolumn returns 1 if the current row have a child which be also its ancestor. Otherwise it returns 0.
You can specify connect_by_iscycle only if you have specified the nocycle parameter of the CONNECT by clause. Nocycle enables Oracle to return the results of a query this would otherwise fail because of a CONNECT by loop in the data .

Oracle Connect by Tree query three (super verbose)