当前位置:首页 > 开发 > 数据库 > 正文

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

发表于: 2013-03-06   作者:czmmiao   来源:转载   浏览次数:
摘要: 概述 层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成,其语法如下:SELECT [LEVEL] ,column,expression,...FROM table_name[WHERE where_clause][[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];LE

概述

层次化查询,即树型结构查询,是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_id SYS_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

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

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
来自 http://www.2cto.com/database/201108/101766.html Oracle中的select语句可以用start with...c
在oracle中通过connect by prior来实现递归查询[zt] 收集的几条在oracle中通过connect by prior来实
一 connect by prior ....start with 废话少说上测试代码. create table test_table_c(id number pr
Syntax 1 CONNECT BY [NOCYCLE] <condition> START WITH <condition> Syntax 2 START W
查找员工编号为7369的领导: 1 SELECT LEVEL,E.* FROM EMP E CONNECT BY PRIOR E.MGR = E.EMPNO STAR
今天有空学习了Oracle的递归查询connect by语句,想借助博客把我的理解与你分享,如果有什么不足请
在做项目中,我们经常会遇到树形数据,在oracle树查询的最重要的就是select…start with…connect b
在做项目中,我们经常会遇到树形数据,在oracle树查询的最重要的就是select…start with…connect b
ORACLE是一个关系数据库管理系统,它用表的形式组织数据,在某些表中的数据还呈现出树型 结构的联系。
这几个关键字是查询递归数据的,形成一个树状结构。目前只有oracle支持,其他数据都要结合存储过程
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号