Oracle recursive query

Grammar:

Select *  from  with column = ? Connect by [[Prior] T.parentid

For example: Menu table, table structure is as follows

　　

The data are as follows:

　　

First, from the top down

1. Query all sub-menus for a specified ID

Select *  from  with=1 by= T. " PARENT "

Note: From the top down, is to specify a node, query its under all the child nodes (including direct child nodes and non-direct child nodes), so start with the condition of the parent =?;

The connect by is the relationship between record and record when the recursion is specified;

The most important distinction is prior: from the top down, 1 as the parent, so the prior is placed on the other side of the parent field, which means that all the child entries are queried

：

2. Query all subordinate menus of a given ID and menu information for that specified ID

Select *  from  with=1by = t. "  PARENT "

Note: From the top down query, find all child nodes under a specified node and the specified node, so the start with condition Specifies ID =? , which means id =? Data starts to query down (contains the specified ID entry).

The connect by is the relationship between record and record when the recursion is specified;

The most important distinction is prior: from the top down, 1 as the parent, so the prior is placed on the other side of the parent field, which means that all the child entries are queried

：

Second, from the bottom up query

1. Query all parent menus for a given ID

Select *  from  with=prior T. "   PARENT "

Note: Query from bottom up, only "contains this record" query, which is different from the top down query

Prior location: Take 42 as ID, get all of its parent, so put prior on the parent side

：

Iii. Summary

1, from the top down query, you can have two results: contains the specified entry, does not contain the specified entry

2, from the bottom up query, there is only one result: contains the specified entry

3. Location of Prior:

Query from top to bottom: start with column =?

？ As a parent, prior on the child side, representing all the children of the query

Query from bottom up: start with column =?

？ As a child, prior on one side of the parent, indicating that all parents are queried

Oracle recursive query