Oracle hierarchical Query

After checking, hierarchical queries are sometimes called connectby queries, which are unique to oracle. These queries can select data and return results in a hierarchical order. The syntax of the connectby and startwith clauses is as follows: SELECT [LEVEL], COLUMN, expression,... FROMtable [WHEREwhere_clause] [[STARTWITH

After checking, hierarchical queries are sometimes called connect by queries, which are unique to oracle. These queries can select data and return results in a hierarchical order. The connect by and start with clause syntax is as follows: SELECT [LEVEL], COLUMN, expression,... FROM table [WHERE where_clause] [[START

After checking, hierarchical queries are sometimes called connect by queries, which are unique to oracle. These queries can select data and return results in a hierarchical order.

The connect by and start with clause syntax is as follows:

SELECT [LEVEL],COLUMN, expression，... FROM table [WHERE where_clause]  [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

The parameter description is as follows:

• Level: indicates a pseudo column, which is the nth layer of the tree (optional ).
• From table: Only one table can be written and queried from one table.
• Where: limits the returned results.
• Start_condition: defines the starting point of a hierarchical query. You must specify the start with clause when writing a hierarchical query.
• Prior_condition: defines the relationship between the parent row and the Child row (described in the previous article). You must specify the connect by prior clause when writing a hierarchical query.

LEVEL:

Level is a pseudo column that exists in all queries executed by Oracle. It is a numerical value that indicates the hierarchy of nodes in the tree. In hierarchical queries, the level value uses the starting root node as level 1, for example:

select  t.* , level, lpad('',4*level - 1) || name  from T_TEST_WORD  t  start  with pid='-1'  connect  by  prior  id = pid

Example:

Intercept with the where condition:

Select t. *, level, lpad ('', 4 * level-1) | name from T_TEST_WORD t where name! = 'Wuxi 'start with pid = '-1' connect by prior id = pid

Hierarchical query restrictions:

1. After the hierarchical query from statement, if it is a table, it can only be one table and cannot be joined.

2. If view is followed by from, view cannot be joined.

3. when the order by clause is used, the order clause starts after the level is complete, so it is meaningless for the level query, unless you pay special attention to the level to obtain the depth of a row in the level, however, both of them will damage the hierarchy. See siblings sorting in the enhancement feature.

4. In start with, the expression can have subqueries, but not in connect.

From: http://blog.csdn.net/nsj820/article/details/6299276

1. SYS_CONNECT_BY_PATH

Oracle 9iProvidedSys_connect_by_path (column, char)Where column isStable or can be automatically converted to stable. Its main purpose is to display the "path" from the parent node to the current node in the specified mode.This function can only be used in hierarchical queries.

The following are the new features of oracle10g.

2. CONNECT_BY_ISLEAF

In oracle9i, It is very complicated to find the leaf node under the specified root. oracle10g introduces a new function, connect_by_isleaf. If the row value is 0, it indicates it is not a leaf node, 1 indicates a leaf node.

3. CONNECT_BY_ISCYCLE and NOCYCLE keywords

If you start from the root node and find a row and the result is the same as that of the ancestor, a loop occurs, and oracle ReportsORA-01436: connect by loop in userdata,In 9i, only those with an endless loop can be added to or deleted from the tree. In 10g, you can add the nocycle keyword to connect by to avoid loop query operations. In addition, connect_by_iscycle is used to determine which node has a loop. 0 indicates that no loop has occurred, and 1 indicates that a loop has occurred.

4. CONNECT_BY_ROOT

Oracle_by_root is added to Oracle10g, which indicates the value of the same column name of the root node of this row before the column name.

5. sort by SIBLINGS keywords

For hierarchical queries, if order by is used for sorting, for example, order by last_name, the level is obtained after the level is completed, and then sorted by last_name. This breaks down the level, for example, paying special attention to the depth of a row, sorting by level also destroys the level.

In oracle10g, siblings keyword sorting is added.

Syntax:Order siblings

It protects the hierarchy and sorts each level by expre.