Oracle Advanced query Connect by

In order to facilitate learning and testing, all of the examples are created under the Oracle's own user Scott.

SELECT statements in Oracle can be used with the start with ... connect by prior ... clauses implement recursive queries, and connect by is used in structured queries with the basic syntax:

Select ... from
where < filter conditions to filter all records returned >
Start with < root node of the qualifying statement, of course, you can relax the constraints to obtain multiple root nodes, is actually more than a tree >
Connect by [prior] < connection conditions, where prior represents the previous record, for example: Connect by prior t.id = t.parent_id means the ID of the previous record is the parent_id of this record, That is, the father of this record is the previous record >
Let's take a look at the example and query all of the employees of ' KING '. The SQL statements are as follows: [SQL]View PlainCopy

1. SELECT *
2. From scott.emp e
3. Start with e.ename = ' KING '
4. Connect by prior e.empno = E.mgr;

Let's look at another example, which in turn queries all the bosses of ' SMITH '. The SQL statements are as follows: [SQL]View PlainCopy

1. SELECT *
2. From scott.emp e
3. Start with e.ename = ' SMITH '
4. Connect by E.empno = prior e.mgr;

With the above two examples, it is estimated that you should understand almost, and then introduce several fixed partners of connect by.

1. Sys_connect_by_path function
Syntax: Sys_connect_by_path (column name, ' delimiter ').
Function: Begins the traversal from start with, and the path is traversed to form a new string based on the delimiter in the function.

[SQL]View PlainCopy

1. Select Sys_connect_by_path (ename, '/') Ename_tree
2. From scott.emp
3. Start with ename = ' KING '
4. Connect by mgr = prior empno;

Insert a digression, introduce the Sys_connect_by_path function use a small trick, the query row into a column, the table emp all column names with ' | ' Separate the output (hint: You can take the following statement apart to analyze each), the SQL statement is as follows: [SQL]View PlainCopy

1. select max (ltrim (Sys_connect_by_path (Column_name,  ' | '))  column_names  
2.   from   (select column_name, rownum rnum  
3.           from user_ tab_columns  
4.           where table_name =  ' EMP ')   
5.  start WITH&NBSP;RNUM&NBSP;=&NBSP;1&NBSP;&NBSP;
6. connect by rnum = rownum;  

2, Level: In the Structured query results, each row is a node in the structure, levels represent the node in the structure of the hierarchy, the root node is 1, the root node is a child node 2, and so on.
The following SQL statement shows the effect visually:

[SQL]View PlainCopy

1. Select Ename, Sys_connect_by_path (ename, '/') Ename_tree, level
2. From scott.emp
3. Start with ename = ' KING '
4. Connect by mgr = prior empno;

3. Connect_by_root: The value of the corresponding column of the root node of the current node is returned before the column name. Connect_by_isleaf: Returns whether the current node is a leaf node, "yes" returns 1, "No" returns 0.
The following SQL statement shows the effect visually:

[SQL]View PlainCopy

1. Select Sys_connect_by_path (ename, '/') Ename_tree,
2. Connect_by_root ename as root,
3. Connect_by_isleaf as isleaf
4. From scott.emp e
5. Start with e.ename = ' KING '
6. Connect by prior e.empno = E.mgr;

http://blog.csdn.net/feier7501/article/details/21811319

Transferred from: http://blog.csdn.net/feier7501/article/details/21815691

Create tables, initialize data;

[SQL]View PlainCopy 

2. CREATE TABLE Tb_company
4. (
6. company_id INTEGER PRIMARY KEY,
8. Company VARCHAR2 (256),
10. Up_companyid INTEGER
12. );
16. INSERT into tb_company VALUES (0, ' head office ', NULL);
18. INSERT into tb_company VALUES (1, ' Beijing branch ', 0);
20. INSERT into tb_company VALUES (2, ' Shanghai branch ', 0);
22. INSERT into tb_company VALUES (3, ' Haidian Division ', 1);
24. INSERT into tb_company VALUES (4, ' Dongcheng Division ', 1);
26. INSERT into tb_company VALUES (5, ' Huangpu District Division ', 2);
28. INSERT into tb_company VALUES (6, ' Jingan division ', 2);
30. COMMIT;

All data:

Query SQL recursively:

[SQL]View PlainCopy 

1. SELECT (Rpad (",LEVEL-1), '-') | | Company) Company_Name, Connect_by_root Company, connect_by_isleaf, Level , Sys_connect_by_path (company, '/')
2. From Tb_company
3. START with Up_companyid is NULL
4. CONNECT by PRIOR company_id = Up_companyid;

Results:

Description

1. Connect_by_root returns the topmost node of the current node
2. Connect_by_isleaf to determine if the leaf node is not a leaf node if there are child nodes underneath the node
3. Level pseudo-column indicates node depth
4. The Sys_connect_by_path function displays a detailed path, separated by "/"

Query SQL recursively:

[SQL]View PlainCopy 

1. SELECT * from tb_company START with company_id = 1 CONNECT by PRIOR company_id = Up_companyid;

Results:

Oracle Advanced query Connect by