Connect by and start with of Oracle

You can use the connect by and start with clauses in the SELECT command to query the tree structure of the table. The command format is as follows:
Select .....Connect by {prior column name 1 = column name 2 | column name 1 = prior column name 2}
[Start with];
The connect by clause indicates that each row of data is retrieved in a hierarchical order, and that the data in the table is connected to the tree structure.
The 0priory operator must be placed before one of the two columns of the connection relationship. For the parent-child relationship between nodes, the prior Escape Character indicates the parent node on one side,
Represent the subnode on the other side to determine whether the order of the right structure is top-down or bottom-up. In addition to column names,
You can also use column operators. The start with clause is optional to identify the node used as the root node for searching the tree structure.

Example:

Create Table Test2 (empno number, ename varchar2 (30), managerid number );

insert into Test2 values (10000, 'martin ', 0 );
insert into Test2 values (10001, 'binn ', 10000 );
insert into Test2 values (10002, 'davit', 10001 );
insert into Test2 values (10003, 'kyte', 10002 );
insert into Test2 values (10004, 'shre', 10003 );
insert into Test2 values (10006, 'ivan', 10002 );
insert into Test2 values (10007, 'Richard ', 10003 );

Select empno, ename, managerid, level, sys_connect_by_path (empno | ename, '<-') Router
From Test2
Start with ename = 'martin'
-- Connect by empno = prior managerid
Connect by prior empno = managerid
Order by level DESC;

Select empno, ename, managerid, level, sys_connect_by_path (empno | ename, '->') Router
From Test2
Start with ename = 'Richard'
Connect by empno = prior managerid
Order by level DESC;

Summary:

Therefore, the preceding rules can be described as follows:
01) when the prior operator is prior to the parent operator, the child => parent relational tree is obtained.

When prior is before child, get the parent => child relationship tree.

02) When start with is specified, all links that do not contain the specified field are filtered out; otherwise, all links are displayed without any filtering.

The simplest practical example is as follows:

In some projects, you can use the following method to write the data source year in the drop-down list:

Select * from
(Select rownum, to_char (add_months (sysdate, 4), 'yyyy')-rownum
From dual connect by rownum <5 );

Note: The problem of using this function to associate multiple tables is related to the Oracle version. The example is as follows:

Create or replace view view_t1
/*
* 816 does not support this method. In 9201, this method may result in incorrect number of records when querying with fld3 as the condition.
Select *
From T1 A, tcode B
Where a. fld1 = B. fld1 (+)
And a. fld3 = B .C _ DM (+)
Start with fld2 is null
Connect by prior fld2_farther = fld2
*/
Select a. *, nvl (B .C _ MC, A. fld3) fld3_mc from
(
Select *
From T1
Start with fld2 is null
Connect by prior fld2_farther = fld2
) A, tcode B
Where a. fld1 = B. fld1 (+)
And a. fld3 = B .C _ DM (+)

========================================================== ========================================================== =

Related Hierarchical QueryPreviousArticleRefer to the following.
Hierarchical query: level application of hierarchical queries
Http://space.itpub.net/519536/viewspace-623916
Hierarchical query: "tree traversal" in hierarchical queries"
Http://space.itpub.net/519536/viewspace-623809
Hierarchical query: _by_iscycle pseudo column of hierarchical queries
Http://space.itpub.net/519536/viewspace-624032


After the level and connect_by_iscycle pseudo columns, the last available Pseudo ColumnThe name is connect_by_isleaf. As described in the pseudo-column name, it can indicate which are "leaf nodes.
If it is a leaf node, the pseudo column returns "1", and the opposite returns the record "0 ".

1. Look back at the relational "Tree", which is intuitive. The F, G, and E nodes are leaf nodes.
A
/\
B c
//
D e
/\
F g

2. Review the T table data
SEC @ ora10g> select * from T;

X Y Z
------------------------------
A 1
B 2 1
C 3 1
D 4 2
E 5 3
F 6 4
G 7 4

7 rows selected.

3. Check the visual effect of connect_by_isleaf with the level pseudo column.
SEC @ ora10g> Col tree for A16
SEC @ ora10g> select lpad ('', 2 * (level-1) | x tree, connect_by_isleaf from t start with x = 'A' connect by nocycle prior y = z;

Tree connect_by_isleaf
---------------------------------
A 0
B 0
D 0
F 1
G 1
C 0
E 1

7 rows selected.

Is there a sense of openness, right? The connect_by_isleaf function is that simple.

4. How to filter out all the leaf nodes in the above results?
SEC @ ora10g> select lpad ('', 2 * (level-1) | x tree, connect_by_isleaf from t where connect_by_isleaf = 1 start with x = 'A' connect by nocycle prior y = z;

Tree connect_by_isleaf
---------------------------------
F 1
G 1
E 1

5. Obtain the leaf nodes of each layer.
You can understand the following SQLIs the process of eliminating the "Tree" layer by layer.
1) do not eliminate it, because our tree has only four layers.
(1) SQL
SEC @ ora10g> select lpad ('', 2 * (level-1) | X Tree, connect_by_isleaf, level from t where connect_by_isleaf = 1 start with x = 'A' connect by nocycle prior y = Z and level <= 4;

Tree connect_by_isleaf level
-------------------------------------------
F 1 4
G 1 4
E 1 3

(2) Corresponding "tree"
A
/\
B c
//
D e
/\
F g

2) Eliminate Layer 4
(1) SQL
SEC @ ora10g> select lpad ('', 2 * (level-1) | X Tree, connect_by_isleaf, level from t where connect_by_isleaf = 1 start with x = 'A' connect by nocycle prior y = Z and level <= 3;

Tree connect_by_isleaf level
-------------------------------------------
D 1 3
E 1 3

(2) Corresponding "tree"
A
/\
B c
//
D e

3) Eliminate Layer 3
(1) SQL
SEC @ ora10g> select lpad ('', 2 * (level-1) | X Tree, connect_by_isleaf, level from t where connect_by_isleaf = 1 start with x = 'A' connect by nocycle prior y = Z and level <= 2;

Tree connect_by_isleaf level
-------------------------------------------
B 1 2
C 1 2

(2) Corresponding "tree"
A
/\
B c

4) Eliminate Layer 2
(1) SQL
SEC @ ora10g> select lpad ('', 2 * (level-1) | X Tree, connect_by_isleaf, level from t where connect_by_isleaf = 1 start with x = 'A' connect by nocycle prior y = Z and level <= 1;

Tree connect_by_isleaf level
-------------------------------------------
A 1 1

(2) the corresponding "Tree" has only the root node.
A

6. Summary
The level, connect_by_iscycle, and connect_by_isleaf pseudo columns available in hierarchical queries are all proposed from the availability perspective. A combination can produce unexpected results. For more interesting details, please feel free to understand.