开发界
首页 移动开发 在线交易 IT笔记 网络技术 操作系统 企业架构 数据库 考试认证 IT培训 开源软件 数据存储 行业资讯
大家都在看:java   开发   C#   oracle   mysql   android   web开发   学习   sqlserver   linux   asp   php   ajax   javascript   css   html
SQL基础层次化查询START BY ... CONNECT BY PRIOR(原创)
2013-03-06 15:54:48 来源:开发界 作者:admin 栏目:SQLServer 责任编辑:admin
[导读]层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成,其语法如下:

概述

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

示例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

树型结构遍历过程(通过上面的查询来描述)
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

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

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

标签:sql sql数据库
版权所有:转载请注明出处!
分享到:
上一篇InnoDB 引擎独立表空间 innodb_fi.. 下一篇SqlServer触发器、存储过程和函数
您可能还喜欢
今日最新资讯 最新推荐信息
考试认证 更多
开发技术 更多
移动开发 更多

关于开发界 | 合作伙伴 | 联系我们 | 友情链接 | 版权声明 | 网站制作 | 网站地图 | 加入收藏 | 设为首页

Copyright@2012-2016 开发界 京ICP备12027873号