Oracle Connect by usage

1. Basic grammar

select * from table [start with condition1] connect by [prior] id=parentid

• 1
• 2

• 1
• 2

Generally used to find the existence of a parent-child relationship data, that is, tree structure of data, and its returned data can be clearly distinguished from each layer of data.

• Start with Condition1 is used to limit the first layer of data, or the root node data, based on this part of the data to find the second layer of data, and then the second layer of data to find third-tier data and so on.

• Connect by [prior] Id=parentid This section is used to indicate what kind of relationship Oracle is looking for when looking for data, such as finding a second tier of data with the ID of the first layer of data that is recorded in the table. ParentID field to match, if this condition is established then the data that is found is the second layer of data, the same way to find the third layer fourth ... And so on, so to match.

Prior also has a usage:

select * from table [start with condition1] connect by id= [prior] parentid

• 1
• 2

• 1
• 2

• This usage means finding the data from the bottom up, which can be understood as finding the parent from the leaf node, using the ParentID of the first layer of data to match the ID in the table record, and then finding the second layer of data; the one above is finding the leaf node down from the parent node.

Other features

1. Level keyword, which represents the hierarchy number in the tree structure; The first layer is the number 1, the second layer number 2, and then increments.
2. The Connect_by_root method is able to get the value of any field in the result set of the first level of the staging point; 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 = 0connect by prior t.id = t.fid;

• 1
• 2
• 3
• 4

• 1
• 2
• 3
• 4

2.2 Finding upper nodes from leaf nodes

--First, modify the location of the prior keywordselect t.*, level, CONNECT_BY_ ROOT (ID) from tab_test t start  With t.id = 4connect by t.id = prior T.fid; --the second, prior keyword does not change the order of the Id=fid relationships behind  select t.*, level, Connect_by_root (ID) from tab_test t start with t.id = Span class= "Hljs-number" >4connect by prior t.fid = t.id;           

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

3, write a few common use of some other usage

3.1 Generating a numeric sequence result set

• Use RowNum to implement a sequence of 1 to 10.

select rownum from dual connect by rownum<=10;

• 1

• 1

The result set is as follows:

• Use the level to achieve a sequence of 1 to 10.

select level from dual connect by level<=10;

• 1

• 1

The result set is as follows:

3.2 Query the start time, end time, and week 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_number (To_char (sysdate-1,  ' d '))-(RowNum- 1) * 7 as endDate, To_number (to_char (Sysdate, 1 as Weekindex from dualconnect by Span class= "Hljs-keyword" >level<= 12; --Change level to rownum to achieve the same effect              

• 1
• 2
• 3
• 4
• 5
• 6
• 7

• 1
• 2
• 3
• 4
• 5
• 6
• 7

• D indicates the day of the week
• IW represents the week ordinal of a year

3.3 String segmentation, from one row to multiple lines

1. such as splitting 01#02#03#04, a regular string.

select REGEXP_SUBSTR(‘01#02#03#04‘, ‘[^#]+‘, 1, rownum) as newport from dual connect by rownum <= REGEXP_COUNT(‘01#02#03#04‘, ‘[^#]+‘);

• 1
• 2

• 1
• 2

4, omit the prior keyword when the data return policy

Constructs a result set that contains two of data, and then queries the level for data at the layer of the tier.

select t.*, level  from (select 1 as num from dual union select 2 as num from dual ) tconnect by level <= 3;

• 1
• 2
• 3
• 4
• 5
• 6

• 1
• 2
• 3
• 4
• 5
• 6

1. From the above results can be seen to omit the prior keyword when the 1th layer of data is the initial result set, the 2nd layer of data is the initial result set of twice times, the 3rd layer of data is the initial result set of 3 times times, assuming that the initial result set record is N, query m-level records, the number of records returned is: Record.

2. It is important to be careful when omitting the prior keyword to manipulate the data, and the returned data is not necessarily what you expect.

5, look at a few examples below, for multiple result sets when omitting prior keyword, how to get the correct return result

5.1 has the following result set

To achieve 1-5, 20-30 of data increments return 1, 2, 3, 4, 5, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30 total 16 records.

SQL is as follows:

With Temp0 as (Select T.range_num, Regexp_substr (T.range_num,' [^-]+ ',1, 1) Minnum,--min Num regexp_substr (t.range_num, " [^-]+ ', 1, 2) maxnum--Max num from range_table t) select t1.range_num, t2.lv  From temp0 T1 join (select level LV from dual connect BY level <= (select max (maxNum) from temp0)" T2 on (t2.lv >=t1.minnum and t2.lv <=t1.maxnum)               

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13

The above SQL is the first to find the maximum and minimum value of range_num, and then use the Connect by attribute to generate a numeric sequence result set, and finally two result sets associated with the desired results.

5.2 Look at a slightly more complex result set, with the same output format as above

SQL is as follows:

With Temp0 as (Select B.range_num, Regexp_substr (B.range_num,' [^,]+ ',1, c.lv)As Newport, Regexp_substr (Regexp_substr (B.range_num,' [^,]+ ',1, c.lv),' [^-]+ ',1,1)As Minnum, Regexp_substr (Regexp_substr (B.range_num,' [^,]+ ',1, c.lv),' [^-]+ ',1,2)As MaxnumFrom (Select Regexp_count (A.range_num,' [^,]+ ')As CNT, Range_numFrom Range_table a) bJoin (SelectLevel LVFrom dualCONNECTByLevel <=50) C--here 50 means range_num through, the number of splits, here to write dead 50 can also sql dynamic max out on c.lv <= b.cnt) select t1.range_num,t2.lv  From temp0 T1 join (select level LV from dual connect BY level <= (select max (To_number (MaxNum)) Span class= "Hljs-keyword" >from temp0)) T2 on ((t2.lv >=t1.minnum and t2.lv <=t1.maxnum)"            

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21st
• 22

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21st
• 22

Oracle Connect by usage (GO)