Recursive with subquery method of Oracle 11gr2

Time:2020-4-20

Next, I will introduce the recursive with subquery method of Oracle 11gr2 in detail, as follows:


SQL> with emp_data(ename,empno,mgr,l)
 as
 (select ename, empno, mgr, 1 lvl from emp where mgr is null
 union all
 select emp.ename, emp.empno, emp.mgr, ed.l+1
 from emp, emp_data ed
 where emp.mgr = ed.empno
 )
 SEARCH DEPTH FIRST BY ename SET order_by
 select l,
  lpad('*' ,2*l, '*')||ename nm
 from emp_data
 order by order_by
 /

  L   NM
—-  —————
  1   **KING
  2   ****BLAKE
  3   ******ALLEN
  3   ******JAMES
  3   ******MARTIN
  3   ******TURNER
  3   ******WARD
  2   ****CLARK
  3   ******MILLER
  2   ****JONES
  3   ******FORD
  4   ********SMITH
  3   ******SCOTT
  4   ********ADAMS

14 rows selected.

I don’t know how it really works. In my imagination, I can play many new tricks more than the original sys connect by path, such as accumulating by path, more flexible pruning conditions,

With subqueries, also known as CTE (common table expression), are part of the ANSI SQL-99 standard. Oracle introduces with subquery from 9i, which is called subquery factoring.

With subqueries act like inline views. The definition of the inline view is written after the SQL from and can only be referenced once. The with subquery needs to be defined before the reference. Once it is defined, it can be referenced repeatedly by name in the subsequent part of the whole query. From this point of view, it is similar to a temporary table.

Starting with version 11gr2, Oracle supports recursive with, which allows for self reference in the definition of a with subquery. This is nothing new. Other databases such as DB2, Firebird, Microsoft SQL server and PostgreSQL support this feature before oracle. However, for Oracle users, this recursive feature is still very desirable, and many new functions that can’t be done in the past or are very difficult to be achieved can be easily realized by using it. In this chapter, we will explore this exciting new feature and compare it with previous implementation methods (mainly connect by hierarchical query).

Let’s first look at the syntax of this recursive with subquery:

WITH

①  query_name ([c_alias [, c_alias]…])
②  AS (subquery)
③  [search_clause]
④  [cycle_clause]
⑤  [,query_name ([c_alias [, c_alias]…]) AS (subquery) [search_clause] [cycle_clause]]… 

① This is the name of the subquery. Unlike in the past, all the column names of the subquery must be written in parentheses.
② Subquery after as is the query statement, and the recursive part is written here.
③ Traversal order clause, which can specify depth first or breadth first traversal order.
④ Loop clause, used to abort the loop that occurs in traversal.
⑤ If there are other recursive subqueries, the definition is the same as above.

Subquery consists of two members: anchor member and recursive member. They must be combined with union all, and anchor member must be written in front of recursive member.
Anchor member is used to locate the entry of recursion. Anchor member is a select statement, and it cannot contain its own name. This is equivalent to start with in connect by query. The typical writing method is:
Select… From table to traverse where… (start condition)

A recursive member is also a select statement, which is used to define the relationship between the superior and the subordinate. It must contain its own name (i.e. query [name]) and can only be referenced once. Recursion is embodied in the reference to itself. A typical way is to make a connection between query_name and other tables (generally speaking, the tables you want to traverse). The connection conditions indicate the relationship between the upper and lower levels. It must be noted that not all data so far in this query name is visible, only the latest layer of data added in the last recursion. A reference to the query_name column is equivalent to the prior operator in connect by. When you can’t find the lower level that meets the conditions, the traversal will stop; if you have other recursion exit conditions, you can also write them together in where. When the where doesn’t meet the conditions, the traversal will stop, which is the pruning operation when traversing trees and graphs. The earlier it stops, the more efficient it is.

This recursive member is the place where programmers play their creativity. In the past, things that could not be done in connect by, such as summation along the path, quadrature and other operations, are now easy. Sys connect by path is also easy to implement with string concatenation ‘|’.

Search clause and cycle clause are shown in the following examples.

Let’s take a look at an example of the usage of a recursive with subquery.

Example 1:

Let’s take a simple example to find the parent-child relationship from the EMP table of Scott / tiger

Traditional connect by:

SELECT empno
 ,ename
 ,job
 ,mgr
 ,deptno
 ,level
 ,SYS_CONNECT_BY_PATH(ename,'\') AS path
 ,CONNECT_BY_ROOT(ename) AS top_manager
 FROM EMP 
Start with Mgr is null -- Mgr column is empty, indicating that there is no superior, and the employee is already at the highest level. This is the starting point of hierarchical query
CONNECT BY PRIOR empno= mgr;

New recursive with method:

With t (empno, ename, job, Mgr, deptno, the_level, path, top_manager) as (- -- structure must be written out
 Select empno, ename, job, Mgr, deptno -- write the anchor query first, and use the condition of start with
  , 1 as the "level
  , '\' ||ename -- the first section of the path
  Original connect by root
 FROM EMP
 Where Mgr is null -- original start with condition
 Union all -- the following is the recursive part
 Select e.empno, e.ename, e.job, e.mgr, e.deptno -- a new layer of data to be added, from the EMP table to be traversed
  , 1 + T.The "level" -- recursion level, add 1 on the original basis. This is equivalent to the level pseudo column in the connect by query
  , t.path| \ '|e.ename
  Directly inherit the original data, because there is only one root node in each path
 From t, EMP e -- a typical writing method that makes a connection between the subquery itself and the table to be traversed
 Where t.empno = e.mgr -- original connect by condition
)---- with definition ends
SELECT * FROM T
;

Query results:


EMPNO ENAME JOB  MGR DEPTNO THE_LEVEL PATH   TOP_MANAGE
------ ---------- --------- ------ ------- ---------- -------------------------- ----------
 7839 KING PRESIDENT  10  1 \KING   KING
 7566 JONES MANAGER 7839 20  2 \KING\JONES  KING
 7698 BLAKE MANAGER 7839 30  2 \KING\BLAKE  KING
 7782 CLARK MANAGER 7839 10  2 \KING\CLARK  KING
 7499 ALLEN SALESMAN 7698 30  3 \KING\BLAKE\ALLEN  KING
 7521 WARD SALESMAN 7698 30  3 \KING\BLAKE\WARD  KING
 7654 MARTIN SALESMAN 7698 30  3 \KING\BLAKE\MARTIN  KING
 7788 SCOTT ANALYST 7566 20  3 \KING\JONES\SCOTT  KING
 7844 TURNER SALESMAN 7698 30  3 \KING\BLAKE\TURNER  KING
 7900 JAMES CLERK 7698 30  3 \KING\BLAKE\JAMES  KING
 7902 FORD ANALYST 7566 20  3 \KING\JONES\FORD  KING
 7934 MILLER CLERK 7782 10  3 \KING\CLARK\MILLER  KING
 7369 SMITH CLERK 7902 20  4 \KING\JONES\FORD\SMITH KING
 7876 ADAMS CLERK 7788 20  4 \KING\JONES\SCOTT\ADAMS KING

14 rows selected.  

From the result set’s the level and path columns, you can clearly see how the data is stacked layer by layer.

Example 2:

Construct the sequence of equal difference numbers:

Connect by:

This is a very special usage, because there is no hierarchy, only the termination condition of traversal. Like this kind of connect by, I strongly recommend running on a result set with only one row (such as from dual, such as a sub query after aggregation). Running on a multi row set is difficult to control and requires a clear mind.

(all of the following rownum can be changed to level, with the same effect):


SELECT ROWNUM n
 ,ROWNUM*2 n2
 ,DATE '2010-1-1'+ROWNUM-1 dt
 ,ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon 
 FROM DUAL 
CONNECT BY ROWNUM<=10;

result:

         N         N2 DT          MON       
———- ———- ———– ———–
         1          2 2010-01-01  2010-01-01
         2          4 2010-01-02  2010-02-01
         3          6 2010-01-03  2010-03-01
         4          8 2010-01-04  2010-04-01
         5         10 2010-01-05  2010-05-01
         6         12 2010-01-06  2010-06-01
         7         14 2010-01-07  2010-07-01
         8         16 2010-01-08  2010-08-01
         9         18 2010-01-09  2010-09-01
        10         20 2010-01-10  2010-10-01

10 rows selected.

This simple and elegant writing method was first published by mikito harakiri (a Japanese by name) on asktom website (http://asktom.oracle.com), and now it has become popular in Oracle community all over the world. Before this method is found, the general method is to get rownum from a large set (table or view):

SELECT ROWNUM n, ROWNUM*2 n2, DATE '2010-1-1'+ROWNUM-1 dt, ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon 
 From all objects -- all objects is a large system view, which contains enough rows to satisfy the general sequence construction
WHERE ROWNUM<=10;

Here’s how to write recursively with:

WITH t(n,n2,dt,mon) AS (
 Select 1, 2, to "date ('2010-1-1 ','yyyy-mm-dd'), to" date ('2010-1-1 ','yyyy-mm-dd') from dual --- construct the first one first
 UNION ALL
 Select T.N + 1 -- increment 1
 , t.n2 + 2 ---- increasing by 2
 , DT + 1 ---- next day
 , add? Months (Mon, 1) --- next month
 From T -- no connection, because no need, all data can be derived from anchor members
 WHERE t.n<10
 )
SELECT * FROM T;

Everything goes according to the rules, but it still goes wrong:

, add? Months (Mon, 1) --- next month
  *
ERROR at line 6:
ORA-01790: expression must have same datatype as corresponding expression

Change to string:

WITH t(n,n2,dt,mon) AS (
 Select 1, 2, '2010-01-01', '2010-01-01' from dual -- use string to represent date
 UNION ALL
 Select T.N + 1 -- increment 1
 , t.n2 + 2 ---- increasing by 2
 , to char (to date (t.dt, 'yyyy-mm-dd') + 1, 'yyyy-mm-dd') -- first convert to date type, then convert to string type after calculation
 , to char (add ﹣ months (to ﹣ date (t.mon, 'yyyy-mm-dd'), 1), 'yyyy-mm-dd') -- calculate next month by the same method as above
 FROM t
 WHERE t.n<10
 )
SELECT * FROM T;

I was surprised to see this result:

         N         N2 DT         MON
———- ———- ———- ———-
         1          2 2010-01-01 2010-01-01
2.4 2009-12-31 2010-02-01 – DT is decreasing!
         3          6 2009-12-30 2010-03-01
         4          8 2009-12-29 2010-04-01
         5         10 2009-12-28 2010-05-01
         6         12 2009-12-27 2010-06-01
         7         14 2009-12-26 2010-07-01
         8         16 2009-12-25 2010-08-01
         9         18 2009-12-24 2010-09-01
        10         20 2009-12-23 2010-10-01

10 rows selected.

This is the bug of oracel version 11.2.0.1.0, which should be corrected in later versions.

I can’t help but think of other ways to get around:

WITH t(n) AS (
 Select 1 from dual -- Construct the first
 UNION ALL
 Select T.N + 1 -- only integer sequence
 FROM t 
 WHERE t.n<10
 )
SELECT n
 ,n*2 n2
 , date '2010-1-1' + n-1 DT -- date operation in the final query
 ,ADD_MONTHS(DATE '2010-1-1', n-1) mon
 FROM T;

That’s right:

         N         N2 DT          MON
———- ———- ———– ———–
         1          2 2010-01-01  2010-01-01
         2          4 2010-01-02  2010-02-01
         3          6 2010-01-03  2010-03-01
         4          8 2010-01-04  2010-04-01
         5         10 2010-01-05  2010-05-01
         6         12 2010-01-06  2010-06-01
         7         14 2010-01-07  2010-07-01
         8         16 2010-01-08  2010-08-01
         9         18 2010-01-09  2010-09-01
        10         20 2010-01-10  2010-10-01

10 rows selected.

It seems that there is bug in the calculation of date. The solution is to construct an integer sequence first, and then use the integer sequence to construct a date sequence in the final query.

From a single result set connect by example:

SELECT ROWNUM rn,cnt
From (select count (*) CNT from EMP) --- aggregate result set with only one row
CONNECT BY ROWNUM<=cnt;

result:

        RN        CNT
———- ———-
         1         14
         2         14
         3         14
         4         14
         5         14
         6         14
         7         14
         8         14
         9         14
        10         14
        11         14
        12         14
        13         14
        14         14

14 rows selected.

Recursive with:

WITH t(n,cnt) AS (
 Select 1, count (*) CNT from EMP -- Construct the first one first
 UNION ALL
 Select T.N + 1 -- increment 1
 , t.cnt -- this CNT column is obtained from the first layer without any modification
 From T -- no connection, because no connection is needed
 Where T.N < t.cnt -- here we see the function of CNT, which is used to terminate traversal
 )
SELECT * FROM t;

The results are the same as above.

Example 3:

Arrangement and combination of independent events: a cloth bag contains the same number of small balls of four colors. Randomly take four times from the cloth bag and put them back after each time. Now, what is the probability that the total number of colors in four results is equal to 3?

Traditional connect by:

WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
 FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
 , replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
 , count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
 From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
, T3 as (- --- Set T3 breaks the color combination contained in path in T2 into four lines
SELECT id,cnt,SUBSTR(PATH,rn,1) color 
 From T2, T -- Cartesian product, used to change one line in T2 into four lines
 )
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
 FROM t3
GROUP BY id,cnt
Having count (distinct color) = 3 -- each ID contains three colors
;

result:

      PROB
———-
     .5625

This example shows the skill of connect by to simulate permutation and combination. Each layer traversal represents an extraction action, because each time is completely independent. In connect by, only the extraction times (traversal layers) are limited without other conditions. Sys connect by path can string the data of all levels accessed up to now. In level = n, the arrangement and combination of the first n layers are included. You can use this query to see the result set T2 generated in the middle:

WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
 FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
 , replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
 , count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
 From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
SELECT * FROM t2;

        ID PATH              CNT
———- ———- ———-
         1 1111              256
         2 1112              256
         3 1113              256
         4 1114              256
         5 1121              256
         6 1122              256
         7 1123              256
         8 1124              256
         9 1131              256
        10 1132              256
        11 1133              256
… (other results are omitted)

256 rows selected.

It can be seen that the path column already contains all the possible results of four extractions, each of which is given a unique number ID.

If you are curious, you can see the next result set T3:

WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
 FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
 , replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
 , count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
 From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
, T3 as (- --- Set T3 breaks the color combination contained in path in T2 into four lines
SELECT id,cnt,SUBSTR(PATH,rn,1) color 
 From T2, T -- Cartesian product, used to change one line in T2 into four lines
 )
SELECT * FROM t3;

        ID        CNT COLO
———- ———- —-
         1        256 1
         1        256 1
         1        256 1
         1        256 1
         2        256 1
         2        256 1
         2        256 1
         2        256 2
         3        256 1
         3        256 1
         3        256 1
         3        256 3
         4        256 1
         4        256 1
         4        256 1
         4        256 4
… (other results are omitted)

1024 rows selected.

You can see that each row in the T2 set has been split into four rows for subsequent aggregation operations.

Finally, let’s look at the main query of calculating probability:


SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
 FROM t3
GROUP BY id,cnt
HAVING COUNT(DISTINCT color)=3;

Count (distinct color) can calculate the number of colors that are not repeated in each ID, and filter those IDS whose number is not 3 in having.

Group by ID, CNT means to group by ID. Because the CNTs of all rows are the same (all equal to 256), we will not change the result of grouping by adding it in the group. The purpose of adding CNT is to reference in the query.
The last two consecutive levels of count function means to aggregate the grouping results into one row and calculate the number of rows of ID satisfying the condition. Divide by CNT to get the probability we want.

This example is an example of unconditional traversal on the result set of multiple rows. As mentioned above, special care should be taken, because there is no relationship between the upper level and the lower level. As the number of layers increases, the amount of data increases significantly.

Recursive with:

WITH T AS (
Select rownum RN -- construct a result set of 1,2,3,4 first
 FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 (distinct_colors, LVL) as (- -- two columns: all non repeating colors, hierarchy
 Select '\' ||rn, 1 -- the first layer is the most basic table of four colors
 FROM t
 UNION ALL
 Select case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has not appeared before
   Then T2. Distinct|colors| \ '\' |t.rn --- spell
  Else t2.distinct_colors
  END 
  , t2.lvl + 1 -- increasing number of layers
 FROM t, t2
 Where T2. LVL < 4 -- the condition of recursive exit: the number of times reaches the limit
)
Select count (case when length (distinct colors) - length (replace (distinct colors, '\')) = 3 then 1 end) --- three slashes appear
 /COUNT(*) 
FROM t2 
Where LVL = 4 -- similar to connect by, we only need to observe the data of the last layer, which already contains the colors of all layers
;

In the recursive with subquery T2, we see that it uses a case expression to splice colors that have not appeared before into distinct_colors. This case is the beauty of recursion with. Using sys connect by path can’t achieve conditional splicing.

At last, we use a skill to convert the number of colors into the number of slashes, because when we construct data, each color is preceded by a slash. In order to find out the number of times a character appears in a string, we use the following method:

First, find the total length of the string;

Use the replace function to remove the character from the string, and then find the length again;

The difference between the two lengths is the number of characters removed.

Case function sets the mark that meets the condition to 1, otherwise it is null. Then another count function can calculate the number of rows that meet the condition, because null is not included in count.

The nesting of count and case is also a common skill in aggregation.

For the calculation of this color number, we can also make conditional accumulation in the process of recursion, so that we can directly use:

WITH T AS (
Select rownum RN -- construct a result set of 1,2,3,4 first
 FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 (distinct colors, LVL, distinct colors, CNT) as (- -- two columns: all unrepeated colors, levels, number of unrepeated colors
 Select '\' |rn, 1,1 -- the first layer is the most basic table of four colors
 FROM t
 UNION ALL
 Select case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has not appeared before
   Then T2. Distinct|colors| \ '\' |t.rn --- spell
  Else t2.distinct_colors
  END 
  , t2.lvl + 1 -- increasing number of layers
  , case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has never appeared
   Then t2.distinct? Colors? CNT + 1 --- color number accumulation
  Else t2.distinct ﹣ colors ﹣ CNT ---- the number of colors has not changed
  END 
 FROM t, t2
 Where T2. LVL < 4 -- the condition of recursive exit: the number of times reaches the limit
)
Select count (case when distinct? Colors? CNT = 3 then 1 end) -- three slashes appear
 /COUNT(*) 
FROM t2 
Where LVL = 4 -- similar to connect by, we only need to observe the data of the last layer, which already contains the colors of all layers
;

Example 4:

Construct a second order sequence of equal difference numbers: the difference of the items in this sequence is an sequence of equal difference numbers

For example: 1,3,6,10,15,21

Use connect by:


SELECT LEVEL, SUM(LEVEL) OVER(ORDER BY LEVEL) n
 FROM DUAL
CONNECT BY LEVEL<=10;

result:

    LEVEL          N
———- ———-
         1          1
         2          3
         3          6
         4         10
         5         15
         6         21
         7         28
         8         36
         9         45
        10         55

10 rows selected.

Because there is only one path, it is easy to use the analysis function sum.

Recursive with:

WITH t(lvl,n) AS (
 Select 1,1 from dual -- Construct the first one first
 UNION ALL
 Select t.lvl + 1, t.lvl + 1 + T.N ---- the increase of N itself is an equal difference sequence, i.e. the new t.lvl
 From T -- no connection, because no connection is needed
 Where t.lvl < 10 ---- stop when you find 10
 )
SELECT * FROM T;

result:

       LVL          N
———- ———-
         1          1
         2          3
         3          6
         4         10
         5         15
         6         21
         7         28
         8         36
         9         45
        10         55
10 rows selected.

Example 5:

Construction of Fibonacci series: it refers to such a series. From the third term, each term is equal to the sum of the first two terms.
1,1,2,3,5,8,13,21,……

The traditional connect by method can’t work out, but the model supported by 10g or above can be easily constructed:

SELECT rn,n
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=10)
MODEL RETURN UPDATED ROWS
 DIMENSION BY (rn)
 MEASURES (1 n)
 RULES ( 
 N [any] order by RN = decode (CV (RN), 1,1,2,1, n [cv() - 2] + n [cv() - 1]) -- Construct the first two with decode, and assign the rest as the sum of the last two
 )
/

        RN          N
———- ———-
         1          1
         2          1
         3          2
         4          3
         5          5
         6          8
         7         13
         8         21
         9         34
        10         55

10 rows selected.

Write recursively with:

WITH t(n,last_n,cnt) AS (
 Select 1,0,1 from dual -- Construct the first one first
 UNION ALL
 Select T.N + t.last_n, T.N, t.cnt + 1 -- sum of the first two
 From T -- no connection, because no connection is needed
 Where t.cnt < 10 ---- stop when you find 10
 )
SELECT n FROM T;

         N
———-
         1
         1
         2
         3
         5
         8
        13
        21
        34
        55

10 rows selected.

Example 6:

Arrangement and combination:

All combination C (3,5) of 3 out of 5:

Connect by:

SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath 
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6) 
WHERE LEVEL=3
Connect by RN < prior RN and level < = 3 -- force to sort in descending order, so that other combinations with the same order but different order are excluded
;

XMLPATH
————–
,5,4,3
,5,4,2
,5,4,1
,5,3,2
,5,3,1
,5,2,1
,4,3,2
,4,3,1
,4,2,1
,3,2,1       

Recursive with:

WITH t AS (
 SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6
 )
, T2 (RN, xmlpath, LVL) as (- --- three columns: current node value, path, number of layers
Select RN, '|||||||||||||||||||||||||||||
UNION ALL
Select t.rn, t2.xmlpath|, '| t.rn, T2. LVL + 1 -- the current node is spliced into the access path, and the number of layers is increased
 FROM t2, t
 WHERE t2.rn<t.rn AND t2.lvl<3
)
SELECT xmlpath FROM t2 WHERE lvl=3;

XMLPATH
———–
,1,2,3
,1,2,4
,1,2,5
,1,3,4
,1,3,5
,1,4,5
,2,3,4
,2,3,5
,2,4,5
,3,4,5

10 rows selected.

For example, P (3,5) can be written as follows:


SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath 
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) 
WHERE LEVEL=3
CONNECT BY NOCYCLE rn<>PRIOR rn AND LEVEL<=3;

XMLPATH
———-
,1,2,3
,1,2,4
,1,2,5
,1,3,2
,1,3,4
,1,3,5
,1,4,2
,1,4,3
,1,4,5
,1,5,2
,1,5,3
,1,5,4
,2,1,3
,2,1,4
… (other results are omitted)

60 rows selected.

Compared with the combination writing method just now, RN < prior RN becomes nocycle RN < > prior RN, which means that as long as RN does not appear, we need all the permutations, not just the descending ones. Note the nocycle in it. This one is only available on 10g.

What happens if you don’t write this nocycle?


SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath 
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) 
WHERE LEVEL=3
CONNECT BY rn<>PRIOR rn AND LEVEL<=3;

ERROR:
ORA-01436: CONNECT BY loop in user data

As you can see, this nocycle is very important. Oracle does not allow loops in the traversal order.

In recursive with, nocycle is written as follows:

WITH t AS (
 SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6
 )
, T2 (RN, xmlpath, LVL) as (- --- three columns: current node value, path, number of layers
Select RN, '|||||||||||||||||||||||||||||
UNION ALL
Select t.rn, t2.xmlpath|, '| t.rn, T2. LVL + 1 -- the current node is spliced into the access path, and the number of layers is increased
 FROM t2, t
 WHERE t2.rn<>t.rn AND t2.lvl<3
)
Cycle RN set cycle flag to 'y' default 'n' --- this cycle flag is a pseudo column name and value defined by itself, which can play the same role as connect by iscycle
SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';

result:

XMLPA
SQL> with emp_data(ename,empno,mgr,l)
  2    as
  3     (select ename, empno, mgr, 1 lvl from emp where mgr is null
  4      union all
  5      select emp.ename, emp.empno, emp.mgr, ed.l+1
  6        from emp, emp_data ed
  7       where emp.mgr = ed.empno
  8     )
  9    SEARCH DEPTH FIRST BY ename SET order_by
10   select l,
11         lpad(‘*’ ,2*l, ‘*’)||ename nm
12     from emp_data
13    order by order_by
14   /

  L   NM
—-  —————
  1   **KING
  2   ****BLAKE
  3   ******ALLEN
  3   ******JAMES
  3   ******MARTIN
  3   ******TURNER
  3   ******WARD
  2   ****CLARK
  3   ******MILLER
  2   ****JONES
  3   ******FORD
  4   ********SMITH
  3   ******SCOTT
  4   ********ADAMS

14 rows selected.

I don’t know how it really works. In my imagination, I can play many new tricks more than the original sys connect by path, such as accumulating by path, more flexible pruning conditions,

With subqueries, also known as CTE (common table expression), are part of the ANSI SQL-99 standard. Oracle introduces with subquery from 9i, which is called subquery factoring.

With subqueries act like inline views. The definition of the inline view is written after the SQL from and can only be referenced once. The with subquery needs to be defined before the reference. Once it is defined, it can be referenced repeatedly by name in the subsequent part of the whole query. From this point of view, it is similar to a temporary table.

Starting with version 11gr2, Oracle supports recursive with, which allows for self reference in the definition of a with subquery. This is nothing new. Other databases such as DB2, Firebird, Microsoft SQL server and PostgreSQL support this feature before oracle. However, for Oracle users, this recursive feature is still very desirable, and many new functions that can’t be done in the past or are very difficult to be achieved can be easily realized by using it. In this chapter, we will explore this exciting new feature and compare it with previous implementation methods (mainly connect by hierarchical query).

Let’s first look at the syntax of this recursive with subquery:

WITH

①  query_name ([c_alias [, c_alias]…])
②  AS (subquery)
③  [search_clause]
④  [cycle_clause]
⑤  [,query_name ([c_alias [, c_alias]…]) AS (subquery) [search_clause] [cycle_clause]]… 

① This is the name of the subquery. Unlike in the past, all the column names of the subquery must be written in parentheses.
② Subquery after as is the query statement, and the recursive part is written here.
③ Traversal order clause, which can specify depth first or breadth first traversal order.
④ Loop clause, used to abort the loop that occurs in traversal.
⑤ If there are other recursive subqueries, the definition is the same as above.

Subquery consists of two members: anchor member and recursive member. They must be combined with union all, and anchor member must be written in front of recursive member.

Anchor member is used to locate the entry of recursion. Anchor member is a select statement, and it cannot contain its own name. This is equivalent to start with in connect by query. The typical writing method is:

Select… From table to traverse where… (start condition)

A recursive member is also a select statement, which is used to define the relationship between the superior and the subordinate. It must contain its own name (i.e. query [name]) and can only be referenced once. Recursion is embodied in the reference to itself. A typical way is to make a connection between query_name and other tables (generally speaking, the tables you want to traverse). The connection conditions indicate the relationship between the upper and lower levels. It must be noted that not all data so far in this query name is visible, only the latest layer of data added in the last recursion. A reference to the query_name column is equivalent to the prior operator in connect by. When you can’t find the lower level that meets the conditions, the traversal will stop; if you have other recursion exit conditions, you can also write them together in where. When the where doesn’t meet the conditions, the traversal will stop, which is the pruning operation when traversing trees and graphs. The earlier it stops, the more efficient it is.

This recursive member is the place where programmers play their creativity. In the past, things that could not be done in connect by, such as summation along the path, quadrature and other operations, are now easy. Sys connect by path is also easy to implement with string concatenation ‘|’.

Search clause and cycle clause are shown in the following examples.

Let’s take a look at an example of the usage of a recursive with subquery.

Example 1:

Let’s take a simple example to find the parent-child relationship from the EMP table of Scott / tiger

Traditional connect by:

SELECT empno
 ,ename
 ,job
 ,mgr
 ,deptno
 ,level
 ,SYS_CONNECT_BY_PATH(ename,'\') AS path
 ,CONNECT_BY_ROOT(ename) AS top_manager
 FROM EMP 
Start with Mgr is null -- Mgr column is empty, indicating that there is no superior, and the employee is already at the highest level. This is the starting point of hierarchical query
CONNECT BY PRIOR empno= mgr;

New recursive with method:

With t (empno, ename, job, Mgr, deptno, the_level, path, top_manager) as (- -- structure must be written out
 Select empno, ename, job, Mgr, deptno -- write the anchor query first, and use the condition of start with
  , 1 as the "level
  , '\' ||ename -- the first section of the path
  Original connect by root
 FROM EMP
 Where Mgr is null -- original start with condition
 Union all -- the following is the recursive part
 Select e.empno, e.ename, e.job, e.mgr, e.deptno -- a new layer of data to be added, from the EMP table to be traversed
  , 1 + T.The "level" -- recursion level, add 1 on the original basis. This is equivalent to the level pseudo column in the connect by query
  , t.path| \ '|e.ename
  Directly inherit the original data, because there is only one root node in each path
 From t, EMP e -- a typical writing method that makes a connection between the subquery itself and the table to be traversed
 Where t.empno = e.mgr -- original connect by condition
)---- with definition ends
SELECT * FROM T
;

Query results:

EMPNO ENAME      JOB          MGR  DEPTNO  THE_LEVEL PATH                       TOP_MANAGE
—— ———- ——— —— ——- ———- ————————– ———-
  7839 KING       PRESIDENT             10          1 \KING                      KING
  7566 JONES      MANAGER     7839      20          2 \KING\JONES                KING
  7698 BLAKE      MANAGER     7839      30          2 \KING\BLAKE                KING
  7782 CLARK      MANAGER     7839      10          2 \KING\CLARK                KING
  7499 ALLEN      SALESMAN    7698      30          3 \KING\BLAKE\ALLEN          KING
  7521 WARD       SALESMAN    7698      30          3 \KING\BLAKE\WARD           KING
  7654 MARTIN     SALESMAN    7698      30          3 \KING\BLAKE\MARTIN         KING
  7788 SCOTT      ANALYST     7566      20          3 \KING\JONES\SCOTT          KING
  7844 TURNER     SALESMAN    7698      30          3 \KING\BLAKE\TURNER         KING
  7900 JAMES      CLERK       7698      30          3 \KING\BLAKE\JAMES          KING
  7902 FORD       ANALYST     7566      20          3 \KING\JONES\FORD           KING
  7934 MILLER     CLERK       7782      10          3 \KING\CLARK\MILLER         KING
  7369 SMITH      CLERK       7902      20          4 \KING\JONES\FORD\SMITH     KING
  7876 ADAMS      CLERK       7788      20          4 \KING\JONES\SCOTT\ADAMS    KING

14 rows selected.  

From the result set’s the level and path columns, you can clearly see how the data is stacked layer by layer.

Example 2:

Construct the sequence of equal difference numbers:

Connect by:

This is a very special usage, because there is no hierarchy, only the termination condition of traversal. Like this kind of connect by, I strongly recommend running on a result set with only one row (such as from dual, such as a sub query after aggregation). Running on a multi row set is difficult to control and requires a clear mind.

(all of the following rownum can be changed to level, with the same effect):
SELECT ROWNUM n
 ,ROWNUM*2 n2
 ,DATE '2010-1-1'+ROWNUM-1 dt
 ,ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon 
 FROM DUAL 
CONNECT BY ROWNUM<=10;

result:

        N         N2 DT          MON       
———- ———- ———– ———–
         1          2 2010-01-01  2010-01-01
         2          4 2010-01-02  2010-02-01
         3          6 2010-01-03  2010-03-01
         4          8 2010-01-04  2010-04-01
         5         10 2010-01-05  2010-05-01
         6         12 2010-01-06  2010-06-01
         7         14 2010-01-07  2010-07-01
         8         16 2010-01-08  2010-08-01
         9         18 2010-01-09  2010-09-01
        10         20 2010-01-10  2010-10-01

10 rows selected.

This simple and elegant writing method was first published by mikito harakiri (a Japanese by name) on asktom website (http://asktom.oracle.com), and now it has become popular in Oracle community all over the world. Before this method is found, the general method is to get rownum from a large set (table or view):

SELECT ROWNUM n, ROWNUM*2 n2, DATE '2010-1-1'+ROWNUM-1 dt, ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon 
 From all objects -- all objects is a large system view, which contains enough rows to satisfy the general sequence construction
WHERE ROWNUM<=10;

Here’s how to write recursively with:

WITH t(n,n2,dt,mon) AS (
 Select 1, 2, to "date ('2010-1-1 ','yyyy-mm-dd'), to" date ('2010-1-1 ','yyyy-mm-dd') from dual --- construct the first one first
 UNION ALL
 Select T.N + 1 -- increment 1
 , t.n2 + 2 ---- increasing by 2
 , DT + 1 ---- next day
 , add? Months (Mon, 1) --- next month
 From T -- no connection, because no need, all data can be derived from anchor members
 WHERE t.n<10
 )
SELECT * FROM T;
Everything goes according to the rules, but it still goes wrong:
 , add? Months (Mon, 1) --- next month
  *
ERROR at line 6:
ORA-01790: expression must have same datatype as corresponding expression

Change to string:

WITH t(n,n2,dt,mon) AS (
 Select 1, 2, '2010-01-01', '2010-01-01' from dual -- use string to represent date
 UNION ALL
 Select T.N + 1 -- increment 1
 , t.n2 + 2 ---- increasing by 2
 , to char (to date (t.dt, 'yyyy-mm-dd') + 1, 'yyyy-mm-dd') -- first convert to date type, then convert to string type after calculation
 , to char (add ﹣ months (to ﹣ date (t.mon, 'yyyy-mm-dd'), 1), 'yyyy-mm-dd') -- calculate next month by the same method as above
 FROM t
 WHERE t.n<10
 )
SELECT * FROM T;

I was surprised to see this result:
         N         N2 DT         MON
———- ———- ———- ———-
         1          2 2010-01-01 2010-01-01
2.4 2009-12-31 2010-02-01 – DT is decreasing!
         3          6 2009-12-30 2010-03-01
         4          8 2009-12-29 2010-04-01
         5         10 2009-12-28 2010-05-01
         6         12 2009-12-27 2010-06-01
         7         14 2009-12-26 2010-07-01
         8         16 2009-12-25 2010-08-01
         9         18 2009-12-24 2010-09-01
        10         20 2009-12-23 2010-10-01

10 rows selected.

This is the bug of oracel version 11.2.0.1.0, which should be corrected in later versions.

I can’t help but think of other ways to get around:

WITH t(n) AS (
 Select 1 from dual -- Construct the first
 UNION ALL
 Select T.N + 1 -- only integer sequence
 FROM t 
 WHERE t.n<10
 )
SELECT n
 ,n*2 n2
 , date '2010-1-1' + n-1 DT -- date operation in the final query
 ,ADD_MONTHS(DATE '2010-1-1', n-1) mon
 FROM T;

That’s right:

         N         N2 DT          MON
———- ———- ———– ———–
         1          2 2010-01-01  2010-01-01
         2          4 2010-01-02  2010-02-01
         3          6 2010-01-03  2010-03-01
         4          8 2010-01-04  2010-04-01
         5         10 2010-01-05  2010-05-01
         6         12 2010-01-06  2010-06-01
         7         14 2010-01-07  2010-07-01
         8         16 2010-01-08  2010-08-01
         9         18 2010-01-09  2010-09-01
        10         20 2010-01-10  2010-10-01

10 rows selected.

It seems that there is bug in the calculation of date. The solution is to construct an integer sequence first, and then use the integer sequence to construct a date sequence in the final query.

From a single result set connect by example:

SELECT ROWNUM rn,cnt
From (select count (*) CNT from EMP) --- aggregate result set with only one row
CONNECT BY ROWNUM<=cnt;

result:

        RN        CNT
———- ———-
         1         14
         2         14
         3         14
         4         14
         5         14
         6         14
         7         14
         8         14
         9         14
        10         14
        11         14
        12         14
        13         14
        14         14
14 rows selected.

Recursive with:

WITH t(n,cnt) AS (
 Select 1, count (*) CNT from EMP -- Construct the first one first
 UNION ALL
 Select T.N + 1 -- increment 1
 , t.cnt -- this CNT column is obtained from the first layer without any modification
 From T -- no connection, because no connection is needed
 Where T.N < t.cnt -- here we see the function of CNT, which is used to terminate traversal
 )
SELECT * FROM t;

The results are the same as above.

Example 3:

Arrangement and combination of independent events: a cloth bag contains the same number of small balls of four colors. Randomly take four times from the cloth bag and put them back after each time. Now, what is the probability that the total number of colors in four results is equal to 3?

Traditional connect by:

WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
 FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
 , replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
 , count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
 From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
, T3 as (- --- Set T3 breaks the color combination contained in path in T2 into four lines
SELECT id,cnt,SUBSTR(PATH,rn,1) color 
 From T2, T -- Cartesian product, used to change one line in T2 into four lines
 )
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
 FROM t3
GROUP BY id,cnt
Having count (distinct color) = 3 -- each ID contains three colors
;

result:

      PROB
———-
     .5625

This example shows the skill of connect by to simulate permutation and combination. Each layer traversal represents an extraction action, because each time is completely independent. In connect by, only the extraction times (traversal layers) are limited without other conditions. Sys connect by path can string the data of all levels accessed up to now. In level = n, the arrangement and combination of the first n layers are included. You can use this query to see the result set T2 generated in the middle:

WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
 FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
 , replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
 , count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
 From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
SELECT * FROM t2;

        ID PATH              CNT
———- ———- ———-
         1 1111              256
         2 1112              256
         3 1113              256
         4 1114              256
         5 1121              256
         6 1122              256
         7 1123              256
         8 1124              256
         9 1131              256
        10 1132              256
        11 1133              256
… (other results are omitted)
256 rows selected.

It can be seen that the path column already contains all the possible results of four extractions, each of which is given a unique number ID.

If you are curious, you can see the next result set T3:

WITH t AS (
Select rownum RN -- first construct a result set of 1,2,3,4, each RN represents a color
 FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 as (- --- Set T2 to simulate four independent actions, and the final result will be 4 * 4 * 4 * 4 = 256 lines
Select rownum ID -- construct a unique ID for the following splitting
 , replace (sys ﹣ connect ﹣ by ﹣ path (RN, '@', '@') path ---- use a special character @ as the separator, and remove it with replace at the end
 , count (*) over() CNT -- use the analysis function to calculate the total row and return it as a column
 From T -- this is a set with four lines
Where level = 4 - all we need is the result of the last level. The path already contains all the result combinations taken four times
Connect by level < = 4 ---- no condition, front and back are independent
)
, T3 as (- --- Set T3 breaks the color combination contained in path in T2 into four lines
SELECT id,cnt,SUBSTR(PATH,rn,1) color 
 From T2, T -- Cartesian product, used to change one line in T2 into four lines
 )
SELECT * FROM t3;

        ID        CNT COLO
———- ———- —-
         1        256 1
         1        256 1
         1        256 1
         1        256 1
         2        256 1
         2        256 1
         2        256 1
         2        256 2
         3        256 1
         3        256 1
         3        256 1
         3        256 3
         4        256 1
         4        256 1
         4        256 1
         4        256 4
… (other results are omitted)
1024 rows selected.

You can see that each row in the T2 set has been split into four rows for subsequent aggregation operations.

Finally, let’s look at the main query of calculating probability:


SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
 FROM t3
GROUP BY id,cnt
HAVING COUNT(DISTINCT color)=3;

Count (distinct color) can calculate the number of colors that are not repeated in each ID, and filter those IDS whose number is not 3 in having.

Group by ID, CNT means to group by ID. Because the CNTs of all rows are the same (all equal to 256), we will not change the result of grouping by adding it in the group. The purpose of adding CNT is to reference in the query.

The last two consecutive levels of count function means to aggregate the grouping results into one row and calculate the number of rows of ID satisfying the condition. Divide by CNT to get the probability we want.

This example is an example of unconditional traversal on the result set of multiple rows. As mentioned above, special care should be taken, because there is no relationship between the upper level and the lower level. As the number of layers increases, the amount of data increases significantly.

Recursive with:

WITH T AS (
Select rownum RN -- construct a result set of 1,2,3,4 first
 FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 (distinct_colors, LVL) as (- -- two columns: all non repeating colors, hierarchy
 Select '\' ||rn, 1 -- the first layer is the most basic table of four colors
 FROM t
 UNION ALL
 Select case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has not appeared before
   Then T2. Distinct|colors| \ '\' |t.rn --- spell
  Else t2.distinct_colors
  END 
  , t2.lvl + 1 -- increasing number of layers
 FROM t, t2
 Where T2. LVL < 4 -- the condition of recursive exit: the number of times reaches the limit
)
Select count (case when length (distinct colors) - length (replace (distinct colors, '\')) = 3 then 1 end) --- three slashes appear
 /COUNT(*) 
FROM t2 
Where LVL = 4 -- similar to connect by, we only need to observe the data of the last layer, which already contains the colors of all layers
;

In the recursive with subquery T2, we see that it uses a case expression to splice colors that have not appeared before into distinct_colors. This case is the beauty of recursion with. Using sys connect by path can’t achieve conditional splicing.

At last, we use a skill to convert the number of colors into the number of slashes, because when we construct data, each color is preceded by a slash. In order to find out the number of times a character appears in a string, we use the following method:

First, find the total length of the string;

Use the replace function to remove the character from the string, and then find the length again;

The difference between the two lengths is the number of characters removed.

Case function sets the mark that meets the condition to 1, otherwise it is null. Then another count function can calculate the number of rows that meet the condition, because null is not included in count.

The nesting of count and case is also a common skill in aggregation.

For the calculation of this color number, we can also make conditional accumulation in the process of recursion, so that we can directly use:

WITH T AS (
Select rownum RN -- construct a result set of 1,2,3,4 first
 FROM DUAL
CONNECT BY ROWNUM<=4
)
, T2 (distinct colors, LVL, distinct colors, CNT) as (- -- two columns: all unrepeated colors, levels, number of unrepeated colors
 Select '\' |rn, 1,1 -- the first layer is the most basic table of four colors
 FROM t
 UNION ALL
 Select case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has not appeared before
   Then T2. Distinct|colors| \ '\' |t.rn --- spell
  Else t2.distinct_colors
  END 
  , t2.lvl + 1 -- increasing number of layers
  , case when instr (t2.distinct|colors| '\', '\' |t.rn| '\') = 0 -- this color has never appeared
   Then t2.distinct? Colors? CNT + 1 --- color number accumulation
  Else t2.distinct ﹣ colors ﹣ CNT ---- the number of colors has not changed
  END 
 FROM t, t2
 Where T2. LVL < 4 -- the condition of recursive exit: the number of times reaches the limit
)
Select count (case when distinct? Colors? CNT = 3 then 1 end) -- three slashes appear
 /COUNT(*) 
FROM t2 
Where LVL = 4 -- similar to connect by, we only need to observe the data of the last layer, which already contains the colors of all layers
;

Example 4:

Construct a second order sequence of equal difference numbers: the difference of the items in this sequence is an sequence of equal difference numbers

For example: 1,3,6,10,15,21

Use connect by:


SELECT LEVEL, SUM(LEVEL) OVER(ORDER BY LEVEL) n
 FROM DUAL
CONNECT BY LEVEL<=10;

result:

     LEVEL          N
———- ———-
         1          1
         2          3
         3          6
         4         10
         5         15
         6         21
         7         28
         8         36
         9         45
        10         55

10 rows selected.

Because there is only one path, it is easy to use the analysis function sum.

Recursive with:

WITH t(lvl,n) AS (
 Select 1,1 from dual -- Construct the first one first
 UNION ALL
 Select t.lvl + 1, t.lvl + 1 + T.N ---- the increase of N itself is an equal difference sequence, i.e. the new t.lvl
 From T -- no connection, because no connection is needed
 Where t.lvl < 10 ---- stop when you find 10
 )
SELECT * FROM T;

result:

       LVL          N
———- ———-
         1          1
         2          3
         3          6
         4         10
         5         15
         6         21
         7         28
         8         36
         9         45
        10         55
10 rows selected.

Example 5:

Construction of Fibonacci series: it refers to such a series. From the third term, each term is equal to the sum of the first two terms.

1,1,2,3,5,8,13,21,……

The traditional connect by method can’t work out, but the model supported by 10g or above can be easily constructed:

SELECT rn,n
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=10)
MODEL RETURN UPDATED ROWS
 DIMENSION BY (rn)
 MEASURES (1 n)
 RULES ( 
  N [any] order by RN = decode (CV (RN), 1,1,2,1, n [cv() - 2] + n [cv() - 1]) -- Construct the first two with decode, and assign the rest as the sum of the last two
 )

/
        RN          N
———- ———-
         1          1
         2          1
         3          2
         4          3
         5          5
         6          8
         7         13
         8         21
         9         34
        10         55
10 rows selected.

Write recursively with:

WITH t(n,last_n,cnt) AS (
 Select 1,0,1 from dual -- Construct the first one first
 UNION ALL
 Select T.N + t.last_n, T.N, t.cnt + 1 -- sum of the first two
 From T -- no connection, because no connection is needed
 Where t.cnt < 10 ---- stop when you find 10
 )
SELECT n FROM T;

         N
———-
         1
         1
         2
         3
         5
         8
        13
        21
        34
        55
10 rows selected.

Example 6:

Arrangement and combination:

All combination C (3,5) of 3 out of 5:

Connect by:

SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath 
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6) 
WHERE LEVEL=3
Connect by RN < prior RN and level < = 3 -- force to sort in descending order, so that other combinations with the same order but different order are excluded
;

XMLPATH
————–
,5,4,3
,5,4,2
,5,4,1
,5,3,2
,5,3,1
,5,2,1
,4,3,2
,4,3,1
,4,2,1
,3,2,1

Recursive with:

WITH t AS (
 SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6
 )
, T2 (RN, xmlpath, LVL) as (- --- three columns: current node value, path, number of layers
Select RN, '|||||||||||||||||||||||||||||
UNION ALL
Select t.rn, t2.xmlpath|, '| t.rn, T2. LVL + 1 -- the current node is spliced into the access path, and the number of layers is increased
 FROM t2, t
 WHERE t2.rn<t.rn AND t2.lvl<3
)
SELECT xmlpath FROM t2 WHERE lvl=3;

XMLPATH
———–
,1,2,3
,1,2,4
,1,2,5
,1,3,4
,1,3,5
,1,4,5
,2,3,4
,2,3,5
,2,4,5
,3,4,5
10 rows selected.

For example, P (3,5) can be written as follows:


SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath 
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) 
WHERE LEVEL=3
CONNECT BY NOCYCLE rn<>PRIOR rn AND LEVEL<=3;

XMLPATH
———-
,1,2,3
,1,2,4
,1,2,5
,1,3,2
,1,3,4
,1,3,5
,1,4,2
,1,4,3
,1,4,5
,1,5,2
,1,5,3
,1,5,4
,2,1,3
,2,1,4
… (other results are omitted)

60 rows selected.

Compared with the combination writing method just now, RN < prior RN becomes nocycle RN < > prior RN, which means that as long as RN does not appear, we need all the permutations, not just the descending ones. Note the nocycle in it. This one is only available on 10g.

What happens if you don’t write this nocycle?


SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath 
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) 
WHERE LEVEL=3
CONNECT BY rn<>PRIOR rn AND LEVEL<=3;

ERROR:

ORA-01436: CONNECT BY loop in user data

As you can see, this nocycle is very important. Oracle does not allow loops in the traversal order.

In recursive with, nocycle is written as follows:

WITH t AS (
 SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6
 )
, T2 (RN, xmlpath, LVL) as (- --- three columns: current node value, path, number of layers
Select RN, '|||||||||||||||||||||||||||||
UNION ALL
Select t.rn, t2.xmlpath|, '| t.rn, T2. LVL + 1 -- the current node is spliced into the access path, and the number of layers is increased
 FROM t2, t
 WHERE t2.rn<>t.rn AND t2.lvl<3
)
Cycle RN set cycle flag to 'y' default 'n' --- this cycle flag is a pseudo column name and value defined by itself, which can play the same role as connect by iscycle
SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';

result:

XMLPA

The above is the recursive with subquery method of Oracle 11gr2 introduced by Xiaobian to you. I hope it can help you. If you have any questions, please leave me a message and Xiaobian will reply to you in time. Thank you very much for your support of the developepaer website!

Recommended Today

Apache sqoop

Source: dark horse big data 1.png From the standpoint of Apache, data flow can be divided into data import and export: Import: data import. RDBMS—–>Hadoop Export: data export. Hadoop—->RDBMS 1.2 sqoop installation The prerequisite for installing sqoop is that you already have a Java and Hadoop environment. Latest stable version: 1.4.6 Download the sqoop installation […]