ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 深入理解connect by

深入理解connect by

原创 Linux操作系统 作者:e_soft 时间:2009-04-16 22:34:25 0 删除 编辑
开始学习connect by 的时候有很多的疑惑,在论坛里面拔了好多帖子,加上自己的理解。现整理如下,希望对一些人有所帮助。
connect by 一般用来生成树状结果集。对于scott schema 下的emp表中有empno,mgr这两个字段,通过这两个列能反应出雇员之间是领导与被领导的关系。
有些雇员领导一些人,有些雇员被领导同时又领导别人,有些雇员只被别人领导。雇员与雇员之间的关系就是一种树结构。

我们先来看一个例子:
select rpad(' ',(level-1)*3)||ename from
emp
connect by prior empno = mgr
start with ename='KING';

RPAD('',(LEVEL-1)*3)||ENAME
----------------------------------------------------------------------------------------------------
KING
   JONES
      SCOTT
         ADAMS
      FORD
         SMITH
   BLAKE
      ALLEN
      WARD
      MARTIN
      TURNER
      JAMES
   CLARK
      MILLER

我们来分析一下结果集是怎样生成的。

start with ename='KING'  子句表示以ename='KING'这行记录开始,我们首先定位到这一行。
select * from emp where ename='KING';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
然后按照connect by prior empno = mgr定义的规则去查找子节点。
connect by prior empno = mgr 这个子句描述了通过字段empno,mgr来确立雇员之间的领导关系。
至于prior关键字放在那个字段前面,意义完全不同,prior 放在哪个字段前面,表示以这个字段为父字段,另一个为子字段。

KING 父字段 empno=7839,那么哪些人是KING的直接下属呢?即满足如下条件的人

select ename from emp where mgr = 7839;

ENAME
----------
JONES
BLAKE
CLARK
然后再按照此规则循环下去。

我们想想如果给出的条件是
connect by  empno = prior mgr
start with ename='KING';
结果是什么样的呢?
同样先定位到ename=’KING‘这一条记录,父字段 mgr = null ,那么哪些人是KING的直接下属呢?即满足如下条件的人
scott@ORCL> select ename from emp where empno is null ;

no rows selected
所以按照connect by empno = prior mgr这种规则KING没有下属 。
我们来验证一下
select rpad(' ',(level-1)*3)||ename from
emp
connect by empno = prior mgr
start with ename='KING';

RPAD('',(LEVEL-1)*3)||ENAME
----------------------------------------------------------------------------------------------------
KING

提醒一下connect by empno = prior mgr 与 connect by prior mgr = empno 效果相同。
所以得出如下结论:定位一条记录后,取出父字段的值X ,通过如下方法查找其子节点
select * from table_name where 子字段=X ;


level表示的是每行记录的层数,根节点为1,直接子节点为2,子子节点为3 ,依此类推。
请看如下查询:
col e_name for a30
select level,rpad(' ',(level-1)*3)||ename e_name from
emp
connect by prior empno = mgr
start with ename='KING';
     LEVEL E_NAME
---------- ------------------------------
         1 KING
         2    JONES
         3       SCOTT
         4          ADAMS
         3       FORD
         4          SMITH
         2    BLAKE
         3       ALLEN
         3       WARD
         3       MARTIN
         3       TURNER
         3       JAMES
         2    CLARK
         3       MILLER


二、节点和分支的裁剪

在对树结构进行查询时,可以去掉表中的某些行,也可以剪掉树中的一个分支,使用WHERE子句来限定树型结构中的单个节点,以去掉树中的单个节点,但它却不影响其后代节
点。
col e_name for a30
select rpad(' ',LEVEL*3) ||ENAME e_name
FROM EMP
WHERE ename!='SCOTT'
CONNECT BY PRIOR empno = mgr
START WITH ENAME='KING';

E_NAME
------------------------------
   KING
      JONES
            ADAMS
         FORD
            SMITH
      BLAKE
         ALLEN
         WARD
         MARTIN
         TURNER
         JAMES
      CLARK
         MILLER

13 rows selected.
在这个查询中,仅剪去了树中单个节点SCOTT。若希望剪去树结构中的某个分支,则要用CONNECT BY 子句。CONNECT BY 子句是限定树型结构中的整个分支,既要剪除分支
上的单个节点,也要剪除其后代节点。
例如显示KING领导下的全体雇员信息,除去SCOTT领导的一支。

select rpad(' ',LEVEL*3) ||ENAME e_name
FROM EMP
CONNECT BY PRIOR empno = mgr and ename!='SCOTT'
START WITH ENAME='KING';

E_NAME
------------------------------
   KING
      JONES
         FORD
            SMITH
      BLAKE
         ALLEN
         WARD
         MARTIN
         TURNER
         JAMES
      CLARK
         MILLER

12 rows selected.
这个查询结果就与上个不同,除了剪去单个节点SCOTT外,还将SCOTT的子节点ADAMS剪掉,即把SCOTT这个分支剪掉了。
当然WHERE子句可以和CONNECT BY子句联合使用,这样能够同时剪掉单个节点和树中的某个分支。

三,其实connect by 还有另外一种功能,可以用来生成序列。
如:
select level id from dual
connect by level<=10;
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.
我们来分析一下其工作原理 ,level<=10用来控制循环的次数,即要重复多少次扫描表dual中的内容。第一次扫描得出的结果集的level都是1,第二次扫描的结果集的level都是2,依此类推。可能用文字描述的不太容易懂,下面我们通过试验来说明:
scott@ORCL> create table test as select ename from emp where rownum<=2;

Table created.
scott@ORCL> select level,ename from test connect by level<=2;

     LEVEL ENAME
---------- ----------
         1 SMITH
         2 SMITH
         2 ALLEN
         1 ALLEN
         2 SMITH
         2 ALLEN

6 rows selected.

scott@ORCL>  select level,ename from test connect by level<=3;

     LEVEL ENAME
---------- ----------
         1 SMITH
         2 SMITH
         3 SMITH
         3 ALLEN
         2 ALLEN
         3 SMITH
         3 ALLEN
         1 ALLEN
         2 SMITH
         3 SMITH
         3 ALLEN
         2 ALLEN
         3 SMITH
         3 ALLEN

14 rows selected.

对比一下上面的结果集,我们就能理解这种用法的原理了。

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

上一篇: 转换Unicode(JS)
请登录后发表评论 登录
全部评论

注册时间:2009-03-26

  • 博文量
    28
  • 访问量
    23807