Oracle Connect by Usage supplemental __oracle

1. Basic Grammar

SELECT * from table [start and condition]
    connect by [prior] Id=parentid

It is commonly used to find data that has a parent-child relationship, that is, a tree-structured data, and its return data can clearly differentiate each layer of data.
The start with condition is used to limit the first layer of data, or to call the root node data, to find the second tier of data based on this part of the data, and then to find the third tier of data with the second tier of data. Connect by [Prior] Id=parentid This section is used to indicate what kind of relationship Oracle is looking for when looking for data, for example, to find the second tier of data with the ID of the first level of data to match the ParentID field recorded in the table, If this condition is set up then the data found is the second layer of data, the same as the third layer of the fourth layer ... And so on are all matched by this.

Prior also has a usage:

SELECT * from table [start and condition]
    connect by id= [Prior] ParentID

This usage means looking up data from the bottom up, can be understood as from the leaf node to look up the parent points, with the first layer of data parentid to match the ID in the table record, matching success then find out is the second layer of data; The above is to look down from the parent node to the leaf node.

Other features
The level keyword, which represents the hierarchy number in the tree structure, the first layer is the number 1, the second level is 2, incremented sequentially.
Connect_by_root method, the value of any field in the result set of the first level assembly point can be obtained; connect_by_root (field name).
2, the following to paste two examples

2.1 Finding leaf nodes from the root node

Select t.*, Level, Connect_by_root (ID) from
  tab_test T-
 start with t.id = 0
CONNECT by prior t.id = T.fid;

2.2 Find the upper node from the leaf node

--First, modify prior keyword location
select t.*, Level, Connect_by_root (ID) from
  tab_test T-
 start with t.id = 4
CONNECT by t.id = Prior t.fid;

--the second, the sequence of the Id=fid logical relationships after the prior keyword is switched to
select t.*, Level, Connect_by_root (ID) from
  tab_test T
 start with T.id = 4
connect by prior T.fid = T.id;

3, write a few common use of some other uses

3.1 generates a number sequence result set uses rownum to implement a sequence of 1 to 10.

Select RowNum from dual connect by rownum<=10;

Use level to implement sequences from 1 to 10.

Select level from dual connect by level<=10;

3.2 Query The start time, end time, and number of weeks of the 12 weeks ahead of the current time

Select Sysdate-(To_number (To_char (sysdate-1, ' d '))-1)-
       (rownum-1) * 7 as StartDate,
       sysdate + (7-to_num ber (To_char (sysdate-1, ' d ')))-
       (rownum-1) * 7 as EndDate,
       to_number (To_char (sysdate, ' IW '))-rownum + 1 as W Eekindex
  from dual
connect by level<= 12;--changing level to rownum can achieve the same effect

3.3 string split, from one line to multiple lines

Select Regexp_substr (' 01#02#03#04 ', ' [^#]+ ', 1, rownum) as Newport to 
    dual connect by rownum <= Regexp_count (' 01# 02#03#04 ', ' [^#]+ '];

Reference: http://blog.csdn.net/wang_yunj/article/details/51040029