SQL层次化查询START BY ... CONNECT BY PRIOR(原创)-阿里云开发者社区

开发者社区> ghost丶桃子> 正文

SQL层次化查询START BY ... CONNECT BY PRIOR(原创)

简介:
+关注继续查看

概述

层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成,其语法如下:
SELECT [LEVEL] ,column,expression,...
FROM table_name
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
LEVEL:伪列,用于表示树的层次
start_condition:层次化查询的起始条件,指定阶层的根。
prior_condition:定义父节点和子节点之间的关系,PRIOR指定父节点。作为运算符,PRIOR和加(+)减(-)运算的优先级相同。condition ... PRIOR expr = expr 或者 ... expr = PRIOR expr

例:
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ... 
CONNECT BY PRIOR employee_id = manager_id and PRIOR account_mgr_id = customer_idSYS_CONNECT_BY_PATH

SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的!它一定要和connect by子句合用!第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符!

示例

SQL>  select empno,mgr,ename,job,level from emp
  2  start with empno = 7839
  3* connect by prior empno = mgr
     EMPNO        MGR ENAME    JOB             LEVEL
---------- ---------- -------- ---------- ----------
      7839            KING     PRESIDENT           1
      7566       7839 JONES    MANAGER             2
      7788       7566 SCOTT    ANALYST             3
      7876       7788 ADAMS    CLERK               4
      7902       7566 FORD     ANALYST             3
      7369       7902 SMITH    CLERK               4
      7698       7839 BLAKE    MANAGER             2
      7499       7698 ALLEN    SALESMAN            3
      7521       7698 WARD     SALESMAN            3
      7654       7698 MARTIN   SALESMAN            3
      7844       7698 TURNER   SALESMAN            3
     EMPNO        MGR ENAME    JOB             LEVEL
---------- ---------- -------- ---------- ----------
      7900       7698 JAMES    CLERK               3
      7782       7839 CLARK    MANAGER             2
      7934       7782 MILLER   CLERK               3
SQL> 
select empno,max(sys_connect_by_path(ename,',')) a from emp
  2  start with empno=7839
  3  connect by prior empno=mgr
  4* group by empno
     EMPNO A
---------- ------------------------------
      7839 ,KING
      7844 ,KING,BLAKE,TURNER
      7782 ,KING,CLARK
      7698 ,KING,BLAKE
      7902 ,KING,JONES,FORD
      7521 ,KING,BLAKE,WARD
      7566 ,KING,JONES
      7788 ,KING,JONES,SCOTT
      7654 ,KING,BLAKE,MARTIN
      7934 ,KING,CLARK,MILLER
      7499 ,KING,BLAKE,ALLEN
     EMPNO A
---------- ------------------------------
      7876 ,KING,JONES,SCOTT,ADAMS
      7369 ,KING,JONES,FORD,SMITH
      7900 ,KING,BLAKE,JAMES
14 rows selected.

树型结构遍历过程(通过上面的查询来描述)
1).从根节点开始(即where_clause中的条件,如果为非根节点则分根节点作为根节点开始遍历,如上例empno = 7839)
2).遍历根节点(得到empno = 7839记录的相关信息)
3).判断该节点是否存在子节点,如果有则访问最左侧未被访问的子节点,否则下一步。上例中prior_condition为empno = mgr,表示子节点的mgr等于父节点的empno,即下一条返回记录的mgr应当等于前一条记录的empno
4).当节点为叶节点,则访问完毕。

5).返回到该节点的父节点,直至检索完所有数据

 

=========================================================

格式化层次查询结果,使用2* level - 2个“_”向左填充。修改start with 中的条件,从非根节点开始遍历
SQL>  select ename,level,lpad(ename,length(ename)+(level*2-2),'_') as Ename ,job
  2  from emp
  3  start with ename='JONES'
  4* connect by prior empno = mgr

ENAME                LEVEL ENAME           JOB
--------------- ---------- --------------- ----------
JONES                    1 JONES           MANAGER
SCOTT                    2 __SCOTT         ANALYST
ADAMS                    3 ____ADAMS       CLERK
FORD                     2 __FORD          ANALYST
SMITH                    3 ____SMITH       CLERK

也可以从下向上进行遍历
SQL> select ename,level,lpad(ename,length(ename)+(level*2-2),'_') as Ename ,job
  2  from emp
  3  start with ename='SCOTT'
  4* connect by prior mgr = empno
ENAME                LEVEL ENAME           JOB
--------------- ---------- --------------- ----------
SCOTT                    1 SCOTT           ANALYST
JONES                    2 __JONES         MANAGER
KING                     3 ____KING        PRESIDENT
============================================================
在层次查询中删除节点和分支

通过where子句来过滤SCOTT用户,但SCOTT的下属ADAMS并没有过滤掉
SQL> select ename,level,lpad(ename,length(ename)+(level*2-2),'_') as Ename ,job
  2  from emp
  3  where ename != 'SCOTT'
  4  start with ename='JONES'
  5* connect by prior empno = mgr
ENAME                LEVEL ENAME           JOB
--------------- ---------- --------------- ----------
JONES                    1 JONES           MANAGER
ADAMS                    3 ____ADAMS       CLERK
FORD                     2 __FORD          ANALYST
SMITH                    3 ____SMITH       CLERK

通过将过滤条件由where 子句的内容移动到connect by prior 子句中过滤掉SCOTT及其下属
SQL> 
select ename,level,lpad(ename,length(ename)+(level*2-2),'_') as Ename ,job
  2  from emp
  3  start with ename='JONES'
  4* connect by prior empno = mgr and ename != 'SCOTT'
ENAME                LEVEL ENAME           JOB
--------------- ---------- --------------- ----------
JONES                    1 JONES           MANAGER
FORD                     2 __FORD          ANALYST
SMITH                    3 ____SMITH       CLERK

配合条件查询
SQL> 
select ename,sal,level,lpad(ename,length(ename)+(level*2-2),'_') as Ename ,job
  2  from emp
  3  --where sal > 1500
  4  start with ename='KING'
  5* connect by prior empno = mgr and sal > (select avg(sal) from emp)
ENAME                  SAL      LEVEL ENAME           JOB
--------------- ---------- ---------- --------------- ----------
KING                  5000          1 KING            PRESIDENT
JONES                 2975          2 __JONES         MANAGER
SCOTT                 3000          3 ____SCOTT       ANALYST
FORD                  3000          3 ____FORD        ANALYST
BLAKE                 2850          2 __BLAKE         MANAGER
CLARK                 2450          2 __CLARK         MANAGER

 

参考至:http://blog.csdn.net/robinson_0612/article/details/5616877
                  http://www.51cto.com/art/200705/47640_1.htm
                  http://myiteyeverywell-163-com.iteye.com/blog/1471752

                  http://www.itpub.net/thread-620427-1-1.html

                  http://blog.chinaunix.net/uid-3634-id-2129988.html

                  http://blog.sina.com.cn/s/blog_777a9ccb0101740d.html
                  http://www.cnblogs.com/huanghai223/archive/2010/12/10/1902696.html

本文原创,转载请注明出处、作者

如有错误,欢迎指正
邮箱:czmcj@163.com

作者:czmmiao  文章出处:http://czmmiao.iteye.com/blog/1824805

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
10095 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10886 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13893 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
11893 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
7365 0
1955
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载