oracle中connect by/level/start with怎么用

发布时间:2021-11-10 09:25:56 作者:小新
来源:亿速云 阅读:155

这篇文章主要介绍了oracle中connect by/level/start with怎么用,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。

level, connect_by_isleaf, connect_by_iscycle伪列:

level 就是这个数据属于 哪一个等级,比如PRESIDENT为1,MANAGER为2
connect_by_isleaf 就是树的最末端的值,或者说这个树枝下已经没有树叶了
connect_by_iscycle 导致出现死循环的那个树枝 

通过START WITH . . . CONNECT BY . . .子句来实现SQL的层次查询.
自从Oracle 9i开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。

自从Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”,
如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。

在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点),Oracle 就会报出一个错误提示:“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。

而在 Oracle 10g 中,只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE, 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示“1”,否则就显示“0”。

syntax 1     connect by [nocycle] start with
syntax 2     start with connect by [nocycle]

实验之:scott用户下emp表

SQL>  select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.
 
查找员工编号为7876的领导:

select level,e.* from emp e connect by prior e.mgr = e.empno  start with e.empno = 7876 order by level desc;

     LEVEL      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
         4       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
         3       7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
         2       7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
         1       7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20

"start with" -- this identifies all level=1 nodes in the tree
"connect by" -- describes how to walk from the parent nodes above to their children and their childrens children.

Easiest to use an example on emp. if we start with "where mgr is null", we generate the set of employees that have no mgr (they are the top of the tree).
if we connect by prior empno = /* current */ mgr that will take all of the prior records (the start with at first) and find all records such that the mgr column equals their empno (find all the records of people managed by the people we started with).


使用with语句优化查询结果:优化等级

 with a as
  (select max(level) + 1 lvl
     from emp e
   connect by prior e.mgr = e.empno
    start with e.empno = 7876
    order by level desc)
 select a.lvl 最高等级加1,
        level 当前等级,
        a.lvl - level 优化后等级,
        e.*  from a,
        emp e connect by prior e.mgr = e.empno start with e.empno = 7876 order by level desc;

最高等级加1   当前等级 优化后等级      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
----------- ---------- ---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
          5          4          1       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
          5          3          2       7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
          5          2          3       7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
          5          1          4       7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
        


查找员工编号为7839的所有下属(7839为king):

 select level 等级, e.*
   from emp e
 connect by prior e.empno = e.mgr
  start with e.empno = 7839;
 
      等级      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
         1       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
         2       7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
         3       7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
         4       7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
         3       7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
         4       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
         2       7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
         3       7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
         3       7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
         3       7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
         3       7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
         3       7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
         2       7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
         3       7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10
         

--构造整个的层次结构
 select lpad(' ',level*2,' ')||ename ename,empno,mgr
     from emp
     start with mgr is null
     connect by prior empno = mgr;
     
ENAME                               EMPNO        MGR
------------------------------ ---------- ----------
  KING                               7839
    JONES                            7566       7839
      SCOTT                          7788       7566
        ADAMS                        7876       7788
      FORD                           7902       7566
        SMITH                        7369       7902
    BLAKE                            7698       7839
      ALLEN                          7499       7698
      WARD                           7521       7698
      MARTIN                         7654       7698
      TURNER                         7844       7698
      JAMES                          7900       7698
    CLARK                            7782       7839
      MILLER                         7934       7782

14 rows selected.


so, king is the start with set then jones blake and clark fall under him. each of them becomes the prior record in turn and their trees are expanded.


使用connect by 结合 level构造虚拟行:

select level from dual connect by level < 5;

     LEVEL
----------
         1
         2
         3
         4

使用rownum实现类似的功能:

select rownum from dual connect by level < 5;

    ROWNUM
----------
         1
         2
         3
         4
         
select level from dual connect by rownum <5;

     LEVEL
----------
         1
         2
         3
         4
---------------------待续-----------------------

使用union all构造两层节点的树:

视图如下所示:

 create or replace view tree_view as
 select
  '1' as rootnodeid,
  'xxxx有限责任公司' as treename,
  '-1'  as parent_id
 from dual
 union
 select
   to_char(d.deptno),
   d.dname || '_' ||d.loc,
   '1' as parent_id
  from dept d;

View created.

desc tree_view

 Name                                           Null?   Type
 -------------------------------------------------------------- ---------
 ROOTNODEID                                             VARCHAR2(40)
 TREENAME                                               VARCHAR2(28)
 PARENT_ID                                              VARCHAR2(2)

select  * from tree_view;

ROOTNODEID                               TREENAME                     PA
---------------------------------------- ---------------------------- --
1                                        xxxx有限责任公司             -1
10                                       ACCOUNTING_NEW YORK          1
20                                       RESEARCH_DALLAS              1
30                                       SALES_CHICAGO                1
40                                       OPERATIONS_BOSTON            1

查询语句:

 select level,t.*
   from tree_view t
  start with t.parent_id = '-1'
 connect by prior t.rootnodeid = t.parent_id;

     LEVEL ROOTNODEID                               TREENAME                     PA
---------- ---------------------------------------- ---------------------------- --
         1 1                                        xxxx有限责任公司             -1
         2 10                                       ACCOUNTING_NEW YORK          1
         2 20                                       RESEARCH_DALLAS              1
         2 30                                       SALES_CHICAGO                1
         2 40                                       OPERATIONS_BOSTON            1

--以下为更新内容:
1、先查看总共有几个等级:

 select count(level)
   from emp e
 connect by prior e.empno = e.mgr
  start with e.mgr is null;

 COUNT(LEVEL)
 ------------
           14
          
2、查看每个等级的人数。主要是通过level进行group by

 select count(level)
   from emp e
 connect by prior e.empno = e.mgr
  start with e.mgr is null
  group by level;
 
  COUNT(LEVEL)
------------
           1
           3
           2
           8

3、oracle 10g提供了一个简单的connect_by_isleaf=1, 0 表示非叶子节点

 select level as 等级, connect_by_isleaf as 是否是叶子节点, e.*
   from emp e
 connect by prior e.empno = e.mgr
  start with e.mgr is null;

      等级 是否是叶子节点      EMPNO ENAME                          JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- -------------- ---------- ------------------------------ --------- ---------- ------------------- ----------
         1              0       7839 KING                           PRESIDENT            1981-11-17 00:00:00       5000                    10
         2              0       7566 JONES                          MANAGER         7839 1981-04-02 00:00:00       2975                    20
         3              0       7788 SCOTT                          ANALYST         7566 1987-04-19 00:00:00       3000                    20
         4              1       7876 ADAMS                          CLERK           7788 1987-05-23 00:00:00       1100                    20
         3              0       7902 FORD                           ANALYST         7566 1981-12-03 00:00:00       3000                    20
         4              1       7369 SMITH                          CLERK           7902 1980-12-17 00:00:00        800                    20
         2              0       7698 BLAKE                          MANAGER         7839 1981-05-01 00:00:00       2850                    30
         3              1       7499 ALLEN                          SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
         3              1       7521 WARD                           SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
         3              1       7654 MARTIN                         SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
         3              1       7844 TURNER                         SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
         3              1       7900 JAMES                          CLERK           7698 1981-12-03 00:00:00        950                    30
         2              0       7782 CLARK                          MANAGER         7839 1981-06-09 00:00:00       2450                    10
         3              1       7934 MILLER                         CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.
 

4、sys_connect_by_path

oracle 9i提供了sys_connect_by_path(column,char),其中column 是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。char可以是单字符也可以是多字符,但不能使用列值中包含的字符,而且这个参数必须是常量,且不允许使用绑定变量,这个函数只能使用在层次查询中。

select level as rank,
        connect_by_isleaf as leaf_is_or_not,
        ENAME
   from emp e
 connect by prior e.empno = e.mgr
  start with e.mgr is null;

      RANK LEAF_IS_OR_NOT ENAME
---------- -------------- ------------------------------
         1              0 KING
         2              0 JONES
         3              0 SCOTT
         4              1 ADAMS
         3              1 FORD
         2              0 BLAKE
         3              1 ALLEN
         3              1 WARD
         3              1 MARTIN
         3              1 TURNER
         3              1 JAMES

      RANK LEAF_IS_OR_NOT ENAME
---------- -------------- ------------------------------
         2              0 CLARK
         3              1 MILLER

13 rows selected.

select level as rank,
        connect_by_isleaf as leaf_is_or_not,
        lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '->')  path
   from emp e
 connect by prior e.empno = e.mgr
  start with e.mgr is null;


      RANK LEAF_IS_OR_NOT PATH
---------- -------------- ----------------------------------------
         1              0  ->KING
         2              0    ->KING->JONES
         3              0      ->KING->JONES->SCOTT
         4              1        ->KING->JONES->SCOTT->ADAMS
         3              1      ->KING->JONES->FORD
         2              0    ->KING->BLAKE
         3              1      ->KING->BLAKE->ALLEN
         3              1      ->KING->BLAKE->WARD
         3              1      ->KING->BLAKE->MARTIN
         3              1      ->KING->BLAKE->TURNER
         3              1      ->KING->BLAKE->JAMES

      RANK LEAF_IS_OR_NOT PATH
---------- -------------- ----------------------------------------
         2              0    ->KING->CLARK
         3              1      ->KING->CLARK->MILLER

13 rows selected.


sys_connect_by_path就是从start with开始的地方开始遍历,并记下其遍历到的节点,start with开始的地方被视为根节点,将遍历到的路径根据函数中的
分隔符,组成一个新的字符串,这个功能是强大的。


实例:
--先查询表:
ENAME 13 rows selected.SQL> select * from emp;

     EMPNO ENAME                          JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ------------------------------ --------- ---------- ------------ ---------- ---------- ----------
      7499 ALLEN                          SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD                           SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES                          MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN                         SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE                          MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK                          MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT                          ANALYST         7566 19-APR-87          3000                    20
      7839 KING                           PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER                         SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS                          CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES                          CLERK           7698 03-DEC-81           950                    30

     EMPNO ENAME                          JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ------------------------------ --------- ---------- ------------ ---------- ---------- ----------
      7902 FORD                           ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER                         CLERK           7782 23-JAN-82          1300                    10

13 rows selected.


--接着查询运用start with conncet by prior;
select ename   
from scott.emp    
start with ename = 'KING'    
connect by prior empno = mgr;  

ENAME
----------
KING
JONES
SCOTT
ADAMS
FORD
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES

ENAME
----------
CLARK
MILLER

13 rows selected.

--最后运用sys_connect_by_path

select sys_connect_by_path(ename, '->') "path"    
from scott.emp    
start with ename = 'KING'    
connect by prior empno = mgr; 

path
----------------------------------------
->KING
->KING->JONES
->KING->JONES->SCOTT
->KING->JONES->SCOTT->ADAMS
->KING->JONES->FORD
->KING->BLAKE
->KING->BLAKE->ALLEN
->KING->BLAKE->WARD
->KING->BLAKE->MARTIN
->KING->BLAKE->TURNER
->KING->BLAKE->JAMES

path
----------------------------------------
->KING->CLARK
->KING->CLARK->MILLER

13 rows selected.


5、修剪树枝和节点:


过滤掉编号是7566的数据(修剪节点),他指的是把这个节点给裁掉,但是并没有破坏树结构,它的子节点还是可以正常的显示。

 select level as rank,
        connect_by_isleaf as leaf_is_or_not,
        lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '=>') path,
        e.*
   from emp e
 where e.empno != 7566
 connect by prior e.empno = e.mgr
  start with e.mgr is null;

 
RANK LEAF_IS_OR_NOT PATH                                     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---- -------------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
   1              0  =>KING                                   7839 KING       PRESIDENT            17-NOV-81      5000             10
   3              0      =>KING=>JONES=>SCOTT                 7788 SCOTT      ANALYST         7566 19-APR-87      3000             20
   4              1        =>KING=>JONES=>SCOTT=>ADAMS        7876 ADAMS      CLERK           7788 23-MAY-87      1100             20
   3              1      =>KING=>JONES=>FORD                  7902 FORD       ANALYST         7566 03-DEC-81      3000             20
   2              0    =>KING=>BLAKE                          7698 BLAKE      MANAGER         7839 01-MAY-81      2850             30
   3              1      =>KING=>BLAKE=>ALLEN                 7499 ALLEN      SALESMAN        7698 20-FEB-81      1600    300      30
   3              1      =>KING=>BLAKE=>WARD                  7521 WARD       SALESMAN        7698 22-FEB-81      1250    500      30
   3              1      =>KING=>BLAKE=>MARTIN                7654 MARTIN     SALESMAN        7698 28-SEP-81      1250   1400      30
   3              1      =>KING=>BLAKE=>TURNER                7844 TURNER     SALESMAN        7698 08-SEP-81      1500      0      30
   3              1      =>KING=>BLAKE=>JAMES                 7900 JAMES      CLERK           7698 03-DEC-81       950             30
   2              0    =>KING=>CLARK                          7782 CLARK      MANAGER         7839 09-JUN-81      2450             10

RANK LEAF_IS_OR_NOT PATH                                     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---- -------------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
   3              1      =>KING=>CLARK=>MILLER                7934 MILLER     CLERK           7782 23-JAN-82      1300             10

12 rows selected.


--裁掉编号是7698的节点和它的子节点:

select level as rank,
        connect_by_isleaf as leaf_is_or_not,
        lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '=>') path,
        e.*
   from emp e
 connect by prior e.empno = e.mgr
        and e.empno != 7698
  start with e.mgr is null;

 
 
  RANK LEAF_IS_OR_NOT PATH                                     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---- -------------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
   1              0  =>KING                                   7839 KING       PRESIDENT            17-NOV-81      5000             10
   2              0    =>KING=>JONES                          7566 JONES      MANAGER         7839 02-APR-81      2975             20
   3              0      =>KING=>JONES=>SCOTT                 7788 SCOTT      ANALYST         7566 19-APR-87      3000             20
   4              1        =>KING=>JONES=>SCOTT=>ADAMS        7876 ADAMS      CLERK           7788 23-MAY-87      1100             20
   3              1      =>KING=>JONES=>FORD                  7902 FORD       ANALYST         7566 03-DEC-81      3000             20
   2              0    =>KING=>CLARK                          7782 CLARK      MANAGER         7839 09-JUN-81      2450             10
   3              1      =>KING=>CLARK=>MILLER                7934 MILLER     CLERK           7782 23-JAN-82      1300             10

7 rows selected.


6、connect_by_root的使用,oracle10g新增connect_by_root, 通过这个操作,可以获取树形查询根记录的字段。

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---------- ---------- --------- ---------- ------------ ------ ------ -------
      7499 ALLEN      SALESMAN        7698 20-FEB-81      1600    300      30
      7521 WARD       SALESMAN        7698 22-FEB-81      1250    500      30
      7566 JONES      MANAGER         7839 02-APR-81      2975             20
      7654 MARTIN     SALESMAN        7698 28-SEP-81      1250   1400      30
      7698 BLAKE      MANAGER         7839 01-MAY-81      2850             30
      7782 CLARK      MANAGER         7839 09-JUN-81      2450             10
      7788 SCOTT      ANALYST         7566 19-APR-87      3000             20
      7839 KING       PRESIDENT            17-NOV-81      5000             10
      7844 TURNER     SALESMAN        7698 08-SEP-81      1500      0      30
      7876 ADAMS      CLERK           7788 23-MAY-87      1100             20
      7900 JAMES      CLERK           7698 03-DEC-81       950             30

     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---------- ---------- --------- ---------- ------------ ------ ------ -------
      7902 FORD       ANALYST         7566 03-DEC-81      3000             20
      7934 MILLER     CLERK           7782 23-JAN-82      1300             10

13 rows selected.

--使用connect_by_root,查询
 select level as rank,
        connect_by_isleaf as leaf_is_or_not,
        connect_by_root ename,
        lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '=>') path,
        e.*
   from emp e
 connect by prior e.empno = e.mgr
  start with e.mgr is null;

 
RANK LEAF_IS_OR_NOT CONNECT_BY PATH                                     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---- -------------- ---------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
   1              0 KING        =>KING                                   7839 KING       PRESIDENT            17-NOV-81      5000             10
   2              0 KING          =>KING=>JONES                          7566 JONES      MANAGER         7839 02-APR-81      2975             20
   3              0 KING            =>KING=>JONES=>SCOTT                 7788 SCOTT      ANALYST         7566 19-APR-87      3000             20
   4              1 KING              =>KING=>JONES=>SCOTT=>ADAMS        7876 ADAMS      CLERK           7788 23-MAY-87      1100             20
   3              1 KING            =>KING=>JONES=>FORD                  7902 FORD       ANALYST         7566 03-DEC-81      3000             20
   2              0 KING          =>KING=>BLAKE                          7698 BLAKE      MANAGER         7839 01-MAY-81      2850             30
   3              1 KING            =>KING=>BLAKE=>ALLEN                 7499 ALLEN      SALESMAN        7698 20-FEB-81      1600    300      30
   3              1 KING            =>KING=>BLAKE=>WARD                  7521 WARD       SALESMAN        7698 22-FEB-81      1250    500      30
   3              1 KING            =>KING=>BLAKE=>MARTIN                7654 MARTIN     SALESMAN        7698 28-SEP-81      1250   1400      30
   3              1 KING            =>KING=>BLAKE=>TURNER                7844 TURNER     SALESMAN        7698 08-SEP-81      1500      0      30
   3              1 KING            =>KING=>BLAKE=>JAMES                 7900 JAMES      CLERK           7698 03-DEC-81       950             30

RANK LEAF_IS_OR_NOT CONNECT_BY PATH                                     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---- -------------- ---------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
   2              0 KING          =>KING=>CLARK                          7782 CLARK      MANAGER         7839 09-JUN-81      2450             10
   3              1 KING            =>KING=>CLARK=>MILLER                7934 MILLER     CLERK           7782 23-JAN-82      1300             10

13 rows selected.
   
上述sql等同于如下:
 select level as rank,
        connect_by_isleaf as leaf_is_or_not,
        connect_by_root(ename) root_ename,
        lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '=>') path,
        e.*
   from emp e
 connect by prior e.empno = e.mgr
  start with e.mgr is null; 

RANK LEAF_IS_OR_NOT ROOT_ENAME PATH                                     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---- -------------- ---------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
   1              0 KING        =>KING                                   7839 KING       PRESIDENT            17-NOV-81      5000             10
   2              0 KING          =>KING=>JONES                          7566 JONES      MANAGER         7839 02-APR-81      2975             20
   3              0 KING            =>KING=>JONES=>SCOTT                 7788 SCOTT      ANALYST         7566 19-APR-87      3000             20
   4              1 KING              =>KING=>JONES=>SCOTT=>ADAMS        7876 ADAMS      CLERK           7788 23-MAY-87      1100             20
   3              1 KING            =>KING=>JONES=>FORD                  7902 FORD       ANALYST         7566 03-DEC-81      3000             20
   2              0 KING          =>KING=>BLAKE                          7698 BLAKE      MANAGER         7839 01-MAY-81      2850             30
   3              1 KING            =>KING=>BLAKE=>ALLEN                 7499 ALLEN      SALESMAN        7698 20-FEB-81      1600    300      30
   3              1 KING            =>KING=>BLAKE=>WARD                  7521 WARD       SALESMAN        7698 22-FEB-81      1250    500      30
   3              1 KING            =>KING=>BLAKE=>MARTIN                7654 MARTIN     SALESMAN        7698 28-SEP-81      1250   1400      30
   3              1 KING            =>KING=>BLAKE=>TURNER                7844 TURNER     SALESMAN        7698 08-SEP-81      1500      0      30
   3              1 KING            =>KING=>BLAKE=>JAMES                 7900 JAMES      CLERK           7698 03-DEC-81       950             30

RANK LEAF_IS_OR_NOT ROOT_ENAME PATH                                     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---- -------------- ---------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
   2              0 KING          =>KING=>CLARK                          7782 CLARK      MANAGER         7839 09-JUN-81      2450             10
   3              1 KING            =>KING=>CLARK=>MILLER                7934 MILLER     CLERK           7782 23-JAN-82      1300             10

13 rows selected.


对于层次查询如果用order by排序,比如order by last_name则是先做完层次获得level,然后按last_name 排序,这样破坏了层次,比如特别关注某行的深度,按level 排序,也是会破坏层次的。在oracle10g中,增加了siblings 关键字的排序。

语法:order siblings by

它会保护层次,并且在每个等级中按expre排序。

 select level as rank,
        connect_by_isleaf as leaf_is_or_not,
        lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '=>') path,
        e.*
   from emp e
 connect by prior e.empno = e.mgr
  start with e.mgr is null
  order siblings by e.ename;


RANK LEAF_IS_OR_NOT PATH                                     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---- -------------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
   1              0  =>KING                                   7839 KING       PRESIDENT            17-NOV-81      5000             10
   2              0    =>KING=>BLAKE                          7698 BLAKE      MANAGER         7839 01-MAY-81      2850             30
   3              1      =>KING=>BLAKE=>ALLEN                 7499 ALLEN      SALESMAN        7698 20-FEB-81      1600    300      30
   3              1      =>KING=>BLAKE=>JAMES                 7900 JAMES      CLERK           7698 03-DEC-81       950             30
   3              1      =>KING=>BLAKE=>MARTIN                7654 MARTIN     SALESMAN        7698 28-SEP-81      1250   1400      30
   3              1      =>KING=>BLAKE=>TURNER                7844 TURNER     SALESMAN        7698 08-SEP-81      1500      0      30
   3              1      =>KING=>BLAKE=>WARD                  7521 WARD       SALESMAN        7698 22-FEB-81      1250    500      30
   2              0    =>KING=>CLARK                          7782 CLARK      MANAGER         7839 09-JUN-81      2450             10
   3              1      =>KING=>CLARK=>MILLER                7934 MILLER     CLERK           7782 23-JAN-82      1300             10
   2              0    =>KING=>JONES                          7566 JONES      MANAGER         7839 02-APR-81      2975             20
   3              1      =>KING=>JONES=>FORD                  7902 FORD       ANALYST         7566 03-DEC-81      3000             20

RANK LEAF_IS_OR_NOT PATH                                     EMPNO ENAME      JOB              MGR HIREDATE        SAL   COMM  DEPTNO
---- -------------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
   3              0      =>KING=>JONES=>SCOTT                 7788 SCOTT      ANALYST         7566 19-APR-87      3000             20
   4              1        =>KING=>JONES=>SCOTT=>ADAMS        7876 ADAMS      CLERK           7788 23-MAY-87      1100             20

13 rows selected.

connect_by_iscycle(存在循环,将返回1,否则返回0)

the connect_by_iscycle pseudocolumn returns 1 if the current row has a child which is also its ancestor. otherwise it returns 0.
you can specify connect_by_iscycle only if you have specified the nocycle parameter of the connect by clause. nocycle enables oracle to return the results of a query that would otherwise fail because of a connect by loop in the data.

感谢你能够认真阅读完这篇文章,希望小编分享的“oracle中connect by/level/start with怎么用”这篇文章对大家有帮助,同时也希望大家多多支持亿速云,关注亿速云行业资讯频道,更多相关知识等着你来学习!

推荐阅读:
  1. Oracle系列中事务有什么用
  2. Oracle Study中Oracle等待事件怎么用

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

oracle

上一篇:如何使用Django-3 模板

下一篇:Django中的unittest应用是什么

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》