ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 高级查询start with...connect by...

oracle 高级查询start with...connect by...

原创 Linux操作系统 作者:0x0x0x 时间:2012-07-10 08:09:38 0 删除 编辑
oracle 高级查询start with...connect by...
 
start with定义了层次化查询的起点。connect by prior定义了父行和子行之间的关系。

例如start with employee_id=1 connect by prior employee_id=manager_id

表示字节点的manager_id指向父节点的employee_id。
 
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> --使用Level显示层级关系
SQL> select level,empno,mgr,ename from scott.emp h
  2  start with empno='7839' connect by prior
  3  empno=mgr
  4  order by level;
 
     LEVEL      EMPNO        MGR ENAME
---------- ---------- ---------- ----------
         1       7839            KING
         2       7566       7839 JONES
         2       7698       7839 BLAKE
         2       7782       7839 CLARK
         3       7902       7566 FORD
         3       7521       7698 WARD
         3       7900       7698 JAMES
         3       7934       7782 MILLER
         3       7499       7698 ALLEN
         3       7788       7566 SCOTT
         3       7654       7698 MARTIN
         3       7844       7698 TURNER
         4       7876       7788 ADAMS
         4       7369       7902 SMITH
 
已选择14行。
 
SQL>
SQL> --从非根节点开始递归查询
SQL> select level,empno,mgr,ename from scott.emp h
  2  start with empno='7902' connect by prior
  3  empno=mgr
  4  order by level;
 
     LEVEL      EMPNO        MGR ENAME
---------- ---------- ---------- ----------
         1       7902       7566 FORD
         2       7369       7902 SMITH
SQL>
SQL> --在start with中使用子查询
SQL> select level,empno,mgr,ename from scott.emp h
  2  start with ename=(select d.ename from scott.emp d where d.ename='FORD') connect by prior
  3  empno=mgr
  4  order by level;
 
     LEVEL      EMPNO        MGR ENAME
---------- ---------- ---------- ----------
         1       7902       7566 FORD
         2       7369       7902 SMITH
SQL>
SQL> --从下到上遍历树(从叶节点遍历至根节点)
SQL> select level,empno,mgr,ename from scott.emp h
  2  start with empno='7369' connect by prior
  3  mgr=empno
  4  order by level;
 
     LEVEL      EMPNO        MGR ENAME
---------- ---------- ---------- ----------
         1       7369       7902 SMITH
         2       7902       7566 FORD
         3       7566       7839 JONES
         4       7839            KING
SQL>
SQL> --从层次查询中删除节点和分支
SQL> select level,empno,mgr,ename from scott.emp h
  2  where ename<>'JONES'
  3  start with empno='7839' connect by prior
  4  empno=mgr
  5  order by level;
 
     LEVEL      EMPNO        MGR ENAME
---------- ---------- ---------- ----------
         1       7839            KING
         2       7698       7839 BLAKE
         2       7782       7839 CLARK
         3       7902       7566 FORD
         3       7788       7566 SCOTT
         3       7499       7698 ALLEN
         3       7900       7698 JAMES
         3       7934       7782 MILLER
         3       7521       7698 WARD
         3       7654       7698 MARTIN
         3       7844       7698 TURNER
         4       7876       7788 ADAMS
         4       7369       7902 SMITH
 
已选择13行。
 
SQL>
SQL> --从以上可以看出,ename='JONES'的结果已经被除去,但是如何将其下属的分支一起删除呢?
SQL> --请看以下SQL:
SQL>
SQL> select level,empno,mgr,ename from scott.emp h
  2  start with empno='7839' connect by prior
  3  empno=mgr
  4  and ename<>'JONES'
  5  order by level;
     LEVEL      EMPNO        MGR ENAME
---------- ---------- ---------- ----------
         1       7839            KING
         2       7782       7839 CLARK
         2       7698       7839 BLAKE
         3       7844       7698 TURNER
         3       7900       7698 JAMES
         3       7934       7782 MILLER
         3       7521       7698 WARD
         3       7499       7698 ALLEN
         3       7654       7698 MARTIN
 
已选择9行。
 
SQL>
 
点评:connect by是递归查询中常用的语法,要熟练运用!
 
参考自:oracle 11g sql 开发者指南
 
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26977915/viewspace-735016/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-06-26

  • 博文量
    35
  • 访问量
    91909