oracle查询树结构二

oracle的家族树介绍

 

oracle的家族树为oracle的fens提供了一个很方便的从顶向底,或者从底到顶的查找功能.语法如下:

select column from table_name start with column=value
connect by prior 父主键=子主键

我下面以一个简单的公司雇员为例（就是上下级的信息放在一张表里,但是有一个字段来关联上下级的信息），具体解释oracle的家族树的用法：

表 t_emptree:

1）建表语句：

  ENAME VARCHAR2(20) primary key not null,    ----职员名称

  EMPNO NUMBER not null,     -----职员编号

  MGR   NUMBER default 0 not null  ---上级编号

2)插入数据：

   

 

3）1、从顶到底列出雇员boss的下属信息

SQL> select ename,empno,mgr from t_emptree start with mgr=0

2        connect by prior empno=mgr;

注：因为mgr的type是number所以使用mgr=0。在一般情况下mgr上级编号的type

    是varchar2所以使用mgr is null.

 

start with :表示从哪一条记录开始查。在这儿表示从mgr=0开始查，即从最高的父级开始从上往下查，查出boss的直属下属和其他的下属。

connect by prior：表示根据子主键（mgr）开始查起。 如果反过来，则是根据父主键(empno)查起。

 

2．使用参数level从顶到底列出雇员boss的下属信息

       SQL>  select ename,empno,mgr,level from t_emptree start with mgr=0

  2  connect by prior empno=mgr;

      

  Level：是伪列，表示深度

 

3.使用参数lpad()

SQL>  select lpad('*',4*(level-1),'#')||ename name,empno,mgr,level from t_emptree

  2   start with mgr=0

  3   connect by prior empno=mgr;

 

 

 

lpad( string1, padded_length, [ pad_string ] )： LPad的意思在某个值的左边加上一定的字符，默认的情况下是空格，也就是说，如果等级是一那么就加一个空格，如果是2那么就加两个空格。

例如：lpad('* ',4*(level-1)，’#’)||（注：“||”是必须的）

第一个参数string1(’* ’)表示：在我查询的第一个字段（用||分割）ename的左边加一个字符“*”。

第二个参数padded_length(4*(level-1))表示：根据level 添加不同数目（4*（level-1）-1）个的第三个参数(pad_string)（比如level=2时就添加4个string,其中一个“*”和3个“#”）。

第三个参数pad_string(“#”)表示：显示根据第二个参数的数量减一后的“#”个数。（也就是说，第三个参数显示的数量加上第一个参数显示的数量必须等于第     二个参数的数量）

在一般情况下：

第一个参数是必写的一般是空格。

第二个参数是必写的一般都使用level来进行计算。“||”也是必写的

第三个参数可以不写，默认为空格。

 

 

4、遍历至根（从上至下或从下至上）
a)从某个雇员开始向他的上级列出该雇员的层次结构，如他的直接上司，以及其他的上司。(从底到顶)

SQL> col ename for a30;
SQL>  select lpad(' ',4*(level-1))||ename ename,mgr,empno,level from t_emptree

  2   start with mgr=4 connect by prior mgr=empno;

 

 

col ename for a30：指定列的输出格式: 30个固定字符长度,如果字段的实际长度超过30个字符,那么会换下一行显示.（也可以是a20,a60等）。

 

b)从某个雇员开始向下列出该雇员的层次结构，如他的直接下属，以及其他的下属。（从顶到底）

SQL>select lpad(' ',4*(level-1))||ename ename,mgr,empno,level from t_emptree

 start with mgr=4 connect by prior empno=mgr;

 

注：connect by prior父主键=子主键:表示根据子主键（mgr）开始查起（一般是从顶到底）。 如果反过来，则是根据父主键(empno)查起（一般是从底到顶）。

 

5．列出所有雇员的层次结构

a) 这个子句可以很清楚的完成整个树的从底到顶的遍历功能。(因为条件start with mgr<>0放宽限定条件，以取得多个根结点，实际就是多棵树。所以他清楚的列出了每一个职员的树结构。)

SQL>  select lpad(' ',4*(level-1))||ename ename,empno,mgr,level from t_emptree

  2   start with mgr<>0

  3   connect by empno=prior mgr;(or  connect by prior mgr=empno;)

 

 

b) 这个子句可以很清楚的完成整个树的从顶到底的遍历功能。(因为条件start with mgr<>0放宽限定条件，以取得多个根结点，实际就是多棵树。所以他清楚的列出了每一个职员的树结构。但是因为mgr<>0所以没有显示boss)

SQL>select lpad(' ',4*(level-1))||ename ename,empno,mgr,level from t_emptree

 start with mgr<>0

 connect by prior empno=mgr(or connect by mgr=prior empno)

 

 

 

 

Prior: PRIOR表示上一条记录，比如 CONNECT BY PRIOR empno=mgr就是说上一条记录的empno是本条记录的mgr，即本记录的父亲是上一条记录。同理CONNECT BY PRIOR mgr=empno就是说上一条纪录的mgr是本条纪录的empno，即本纪录的儿子是上一条纪录。

 

 

注意由上层向下层递归与下层向上层递归的区别在于START WITH...CONNECT BY PRIOR...的先后顺序以及 empno= mgr和 mgr = empno 的微小变化!