使用Connect By构造序列

使用Connect By Level构造序列


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as system@LOCAL_TEST


SQL> set pagesize 1000
SQL> 
SQL> Select * From Test Connect By Level<=1;
STR
----------
a
b
c
SQL> Select * From Test Connect By Level<=2;
STR
----------
a
a
b
c
b
a
b
c
c
a
b
c
12 rows selected
SQL> Select * From Test Connect By Level<=3;
STR
----------
a
a
a
b
c
b
a
b
c
c
a
b
c
b
a
a
b
c
b
a
b
c
c
a
b
c
c
a
a
b
c
b
a
b
c
c
a
b
c
39 rows selected


假设表的记录数为n 则行数为=n^1+n^2+...+n^Level 每条记录的子节点为表上所有记录


SQL> Select Level From dual Connect By Level<=3;
     LEVEL
----------
         1
         2
         3
SQL> Select Rownum From dual Connect By Level<=3;
    ROWNUM
----------
         1
         2
         3


SQL>