三十三、Oracle层次查询 connect by prior

素颜马尾好姑娘i 2022-04-06 16:12 217阅读 0赞

#            Oracle层次查询 connect by prior #

### 1、表格初始化脚本 ###

https://download.csdn.net/download/newbie_907486852/10838835

### 2、connect by递归调用 ###

select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;

![在这里插入图片描述][20181209201302581.png]

select rownum,rownum+1,rownum+2 from dual where rownum <=110000;

![在这里插入图片描述][20181209201316367.png]

### 3、connect by层级查询 ###

select empno,mgr, ename, job, hiredate, sal, comm, deptno, level as lv
    from emp
    start with empno = 7839
    connect by (prior empno) = mgr;
    
    
    prior 在 empno前表示对empno进行升序
    prior 在 empno后表示对empno进行降序
    start with empno = 7839表示从这个开始查询
    connect by (prior empno) = mgr; 表示进行层级查询
    level 自动递增

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[20181209201302581.png]: /images/20220406/c8c2afa3cca045f195c7b32d15e1b4a3.png
[20181209201316367.png]: /images/20220406/65f3d0ac7a794aee8aa67f5e7f4438d5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70]: /images/20220406/1a7303adfed24ba6b47dca898190e4d2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220406/a08bd856c1214c11bb3ba53a4f78fc58.png