Recursive query for Oracle with statements

The Oracle with statement can implement the same sequence as the Connect by statement:

Connect by usage

Use RowNum to implement a sequence of 1 to 10.

Select RowNum from dual connect by rownum<=10;

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

Select level from dual connect by level<=10;

With can achieve the same functionality usage:

With C (n) as (select 1 from dualunion allselect n+1 from Cwhere n<10) select n from C;

More Connect by Usage Reference: 51040029/

Query the start time, end time, and number of weeks of the 12 week ahead of the current time:

SelectSysdate-(To_number (To_char (Sysdate-1,' d ')) -1)-(RowNum-1) *7 asStartDate, Sysdate + (7-To_number (To_char (Sysdate-1,' d '))-(RowNum-1) *7 asEndDate, To_number (To_char (Sysdate,' IW '))-RowNum +1 asWeekindex fromDualConnect by Level<= A;--change level to rownum to achieve the same effect

• D indicates the day of the week

• IW represents the week ordinal of a year

• 
  

String splitting, 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 ', ' [^|] +');

Recursive query for Oracle with statements