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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26977915/viewspace-735016/