Database study notes ---SQL --> hierarchical query (START BY... CONNECT BY PR

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

SQL based --> hierarchical query(START BY ... CONNECT BY PRIOR)
Technology of QQ communication group: JavaDream: 251572072
Tutorial download, online communication: a dream of the IT community: www.credream.com
--============================================================================================================
Hierarchical query, the query tree type structure, is one of the frequently used SQL in function, usually by the root node, the parent node, node, leaf nodes, the syntax is as follows:
SELECT [LEVEL] ,column,expression,...
FROM table_name
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

LEVEL: pseudo columns, is used to represent the tree
start_condition: The initial conditions of hierarchical query
prior_condition: Defines the relationship between parent and child nodes

Using start with... Connect by prior began traversal from the root node
SQL> select empno,mgr,ename,job from emp
2 start with empno = 7839
3 connect by prior empno = mgr;

EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------
7839 KING PRESIDENT
7566 7839 JONES MANAGER
7788 7566 SCOTT ANALYST
7876 7788 ADAMS CLERK
7902 7566 FORD ANALYST
7369 7902 SMITH CLERK
7698 7839 BLAKE MANAGER
7499 7698 ALLEN SALESMAN
7521 7698 WARD SALESMAN
7654 7698 MARTIN SALESMAN
7844 7698 TURNER SALESMAN

EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------
7900 7698 JAMES CLERK
7782 7839 CLARK MANAGER
7934 7782 MILLER CLERK

14 rows selected.

Tree traversal process (described by the above query)
1). starting from the root node (i.e., the condition in the where_clause, if a non root node is a root node as root node traversal, as in the example above empno = 7839)
2)Traversing the root node. (related information obtained with empno = 7839 records)
3). determine whether the node has child nodes, if access to the left most have not been accessed node), otherwise, go to the next step
As the example of prior_condition for empno = Mgr, namely the child nodes of the parent node Mgr is equal to empno, in the Mgr 7839 records
4)When the node is a leaf node, the access is completed, otherwise, to)
5)Returned to the parent node. The node, to)
Using the pseudo column level
Connect by prior empno = - note Mgr understanding
--prior said the previous record, i.e. a return records of Mgr should be equal to the previous record of empno
SQL> select level,empno,mgr,ename,job from emp
2 start with ename = 'KING'
3 connect by prior empno = mgr
4 order by level;

LEVEL EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------- ---------
1 7839 KING PRESIDENT
2 7566 7839 JONES MANAGER
2 7698 7839 BLAKE MANAGER
2 7782 7839 CLARK MANAGER
3 7902 7566 FORD ANALYST
3 7521 7698 WARD SALESMAN
3 7900 7698 JAMES CLERK
3 7934 7782 MILLER CLERK
3 7499 7698 ALLEN SALESMAN
3 7788 7566 SCOTT ANALYST
3 7654 7698 MARTIN SALESMAN

LEVEL EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------- ---------
3 7844 7698 TURNER SALESMAN
4 7876 7788 ADAMS CLERK
4 7369 7902 SMITH CLERK

-- get number
SQL> select count(distinct level) "Level" from emp
2 start with ename = 'KING'
3 connect by prior empno = mgr;

Level
----------
4

--Type of hierarchical query results (using the left filled * level - 1 empty)
SQL> col Ename for a30
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename",
3 job
4 from emp
5 start with ename = 'KING'
6 connect by prior empno = mgr;

LEVEL Ename JOB
---------- ------------------------------ ---------
1 KING PRESIDENT
2 JONES MANAGER
3 SCOTT ANALYST
4 ADAMS CLERK
3 FORD ANALYST
4 SMITH CLERK
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 WARD SALESMAN
3 MARTIN SALESMAN
3 TURNER SALESMAN

LEVEL Ename JOB
---------- ------------------------------ ---------
3 JAMES CLERK
2 CLARK MANAGER
3 MILLER CLERK

14 rows selected.

-- starting from non root node traversal (only need to modify the start in the with condition)
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename",
3 job
4 from emp
5 start with ename = 'SCOTT'
6 connect by prior empno = mgr;

LEVEL Ename JOB
---------- ------------------------------ ---------
1 SCOTT ANALYST
2 ADAMS CLERK

-- from bottom to top traversal (connect by exchange in prior conditions, the use of Mgr = empno)
Note that connect by prior Mgr: = empno theory
--prior said the previous record, i.e. a return records of empno should be equal to the previous record of Mgr

SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename",
3 job
4 from emp
5 start with ename = 'SCOTT'
6 connect by prior mgr = empno;

LEVEL Ename JOB
---------- ------------------------------ ---------
1 SCOTT ANALYST
2 JONES MANAGER
3 KING PRESIDENT

-- from bottom to top traversal (prior can also be placed to the right of the equal sign, got the same results)
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename",
3 job
4 from emp
5 start with ename = 'SCOTT'
6 connect by empno = prior mgr;

LEVEL Ename JOB
---------- ------------------------------ ---------
1 SCOTT ANALYST
2 JONES MANAGER
3 KING PRESIDENT

- delete nodes and branches from the level in the query
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename"
3 ,job
4 from emp
5 where ename != 'SCOTT' By the where clause to filter the SCOTT users, but SCOTT does not filter out under ADAMS
6 start with empno = 7839
7 connect by prior empno = mgr;

LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
4 ADAMS CLERK
3 FORD ANALYST
4 SMITH CLERK
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 WARD SALESMAN
3 MARTIN SALESMAN
3 TURNER SALESMAN
3 JAMES CLERK

LEVEL Ename JOB
---------- -------------------- ---------
2 CLARK MANAGER
3 MILLER CLERK

13 rows selected.

The filtering conditions -- by moving the contents of a where clause to the connect by prior clause to filter out SCOTT and its subordinate
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename"
3 ,job
4 from emp
5 start with empno = 7839
6 connect by prior empno = mgr and ename != 'SCOTT';

LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
3 FORD ANALYS
4 SMITH CLERK
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 WARD SALESMA
3 MARTIN SALESMAN
3 TURNER SALESMAN
3 JAMES CLERK
2 CLARK MANAGER

LEVEL Ename JOB
---------- -------------------- ---------
3 MILLER CLERK

12 rows selected.

In the hierarchical query increase the filtering conditions or using a subquery
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename"
3 ,job
4 from emp
5 where sal > 2500
6 start with empno = 7839
7 connect by prior empno = mgr
8 ;

LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
3 SCOTT ANALYST
3 FORD ANALYST
2 BLAKE MANAGER
SQL> select level,
2 lpad(' ',2 * level - 1) || ename as "Ename"
3 ,job
4 from emp
5 where sal > (select avg(sal) from emp)
6 start with empno = 7839
7 connect by prior empno = mgr ;
LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
3 SCOTT ANALYST
3 FORD ANALYST
2 BLAKE MANAGER
2 CLARK MANAGER
6 rows selected.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Isaac at December 12, 2013 - 6:43 AM