connect by 家族树的使用

家族树
语法:
select column from table_name start with column=value
connect by prior 父主键=子主键

1排除单一性和分枝
以ORACLE中的EMP表为例
[例]从顶到底列出各雇员的信息
SQL> select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null
  2  connect by prior empno=mgr;

NAME                                 EMPNO       MGR
---------                                  ---------           ---------
KING                                   7839           
    JONES                           7566            7839
          SCOTT                     7788            7566
                ADAMS              7876            7788

2遍历至根
[例1]现在要从某个雇员开始向他的上级列出该雇员的层次结构
SQL> col ename for a30;
SQL> select lpad(' ',4*(level-1))||ename ename,mgr,empno from scott.emp
  2  start with mgr=7788 connect by prior mgr=empno;
ENAME                                MGR     EMPNO
------------------------------         ---------    ---------
ADAMS                               7788      7876
    SCOTT                           7566       7788
        JONES                       7839       7566
            KING                                        7839

[例2]列出所有雇员的层次结构
SQL> select lpad(' ',4*(level-1))||ename ename,empno,mgr from scott.emp
  2  start with mgr is not null
  3  connect by empno=prior mgr;

ENAME                              EMPNO       MGR
------------------------------       ---------           ---------
SMITH                               7369          7902
    FORD                            7902         7566
        JONES                       7566        7839
            KING                    7839
ALLEN                               7499        7698
    BLAKE                           7698        7839
        KING                        7839
WARD                                7521        7698
    BLAKE                           7698        7839
        KING                        7839
JONES                               7566        7839
    KING                            7839
MARTIN                              7654        7698
    BLAKE                           7698         7839
        KING                        7839
BLAKE                               7698        7839
    KING                            7839
CLARK                               7782         7839
    KING                            7839
SCOTT                               7788         7566
    JONES                           7566        7839

层次查询子句connect by，用于构造层次结果集的查询。
语法：
[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition
说明：
a、START WITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。
b、当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。此时，需要在connect by后面加上NOCYCLE关键字。同时，可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用，用法见示例2。

用法举例：

示例1：显示所有地名关系结构。
SQL> select * from t;
AREA_ID  AREA_NAME  MGR_ID
-------- ---------- ------
86       中国
01       北京       86
02       福建       86
0101     海淀区     01
0102     朝阳区     01
0103     东城区     01
0104     西城区     01
0201     厦门       02
0202     福州       02
020101   湖里       0201
020102   思明       0201
010401   复兴门     0104
010402   西单       0104
已选择13行。
SQL>
SQL> set pagesize 50
SQL> col AreaName for a12
SQL> col Root for a10
SQL> col Path for a24
SQL>
SQL> select rpad( ' ', 2*(level-1), ' ' ) || area_name "AreaName",
  2     connect_by_root area_name "Root",
  3     connect_by_isleaf "IsLeaf",
  4     level ,
  5     SYS_CONNECT_BY_PATH(area_name, '/') "Path"
  6  from t
  7  start with mgr_id is null
  8  connect by prior area_id = mgr_id;
AreaName     Root       IsLeaf      LEVEL Path
------------ ---------- ------ ---------- ------------------------
中国         中国            0          1 /中国
  北京       中国            0          2 /中国/北京
    海淀区   中国            1          3 /中国/北京/海淀区
    朝阳区   中国            1          3 /中国/北京/朝阳区
    东城区   中国            1          3 /中国/北京/东城区
    西城区   中国            0          3 /中国/北京/西城区
      复兴门 中国            1          4 /中国/北京/西城区/复兴门
      西单   中国            1          4 /中国/北京/西城区/西单
  福建       中国            0          2 /中国/福建
    厦门     中国            0          3 /中国/福建/厦门
      湖里   中国            1          4 /中国/福建/厦门/湖里
      思明   中国            1          4 /中国/福建/厦门/思明
    福州     中国            1          3 /中国/福建/福州
已选择13行。
说明：
a、prior：是单一操作符，放在列名的前面，等号左右均可；
b、connect_by_root：是单一操作符，返回当前层的最顶层节点；
c、connect_by_isleaf：是伪列，判断当前层是否为叶子节点，1代表是，0代表否；
d、level：是伪列，显示当前节点层所处的层数；
e、SYS_CONNECT_BY_PATH：是函数，显示当前层的详细路径。  

示例2：找出人事部门中存在跟其他部门互为管理者的人员名单。
SQL> select * from t2;
EMP          DEPT   MGR
------------ ------ ----------
刘涛         总裁办
李飞         总裁办 刘涛
张强         总裁办 刘涛
王鹏         人事   李飞
李华         人事   李飞
张强         人事   李飞
李飞         行政   张强
吴华         行政   张强
已选择8行。
SQL>
SQL> col emp for a12
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp"
  2  from t2
  3  start with dept ='人事'
  4  connect by prior emp = mgr;
ERROR:
ORA-01436: 用户数据中的 CONNECT BY 循环

未选定行

说明：张强和李飞互为管理者，因此，要用nocycle，如下所示：
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp"
  2      from t2
  3      start with dept ='人事'
  4      connect by nocycle prior emp = mgr;
emp
------------
王鹏
李华
张强
  李飞
    王鹏
    李华
  吴华
已选择7行。

SQL>
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp",
  2     connect_by_iscycle "IsCycle"
  3  from t2
  4  start with dept ='人事'
  5  connect by prior emp = mgr;
connect by prior emp = mgr
           *
第 5 行出现错误:
ORA-30930: CONNECT_BY_ISCYCLE 伪列要求 NOCYCLE 关键字

说明：在用connect_by_iscycle定位节点时，也要用nocycle关键字，如下所示：

SQL>
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp",
  2     connect_by_iscycle "IsCycle"
  3  from t2
  4  start with dept ='人事'
  5  connect by nocycle prior emp = mgr;emp          IsCycle
------------ -------
王鹏               0
李华               0
张强               0
  李飞             1
    王鹏           0
    李华           0
  吴华             0
已选择7行。
SQL>

示例3：仅显示第二层（即level=2）省市名称。
SQL> select rpad( ' ', 2*(level-1), ' ' ) || area_name "AreaName"
  2  from t
  3  where level = 2
  4  start with mgr_id is null
  5  connect by prior area_id = mgr_id;
AreaName
------------
  北京
  福建

示例4：用connect by构造序列。
SQL>
SQL> select rownum rn
  2  from dual
  3  connect by rownum<=10;
        RN
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
已选择10行。
SQL> select rownum*2 -1 rn
  2  from dual
  3  connect by rownum<=10;
        RN
----------
         1
         3
         5
         7
         9
        11
        13
        15
        17
        19
已选择10行。

-----------------------------------------
附：建表语句
create table t (area_id varchar2(6), area_name varchar2(10), mgr_id varchar2(6));
insert into t values('86', '中国', null);
insert into t values('01', '北京', '86');
insert into t values('02', '福建', '86');
insert into t values('0101', '海淀区', '01');
insert into t values('0102', '朝阳区', '01');
insert into t values('0103', '东城区', '01');
insert into t values('0104', '西城区', '01');
insert into t values('0201', '厦门', '02');
insert into t values('0202', '福州', '02');
insert into t values('020101', '湖里', '0201');
insert into t values('020102', '思明', '0201');
insert into t values('010401', '复兴门', '0104');
insert into t values('010402', '西单', '0104');
commit;

create table t2 (emp varchar2(10), dept varchar2(6), mgr varchar2(10));
insert into t2 values('刘涛', '总裁办', null);
insert into t2 values('李飞', '总裁办', '刘涛');
insert into t2 values('张强', '总裁办', '刘涛');
insert into t2 values('王鹏', '人事', '李飞');
insert into t2 values('李华', '人事', '李飞');
insert into t2 values('张强', '人事', '李飞');
insert into t2 values('李飞', '行政', '张强');
insert into t2 values('吴华', '行政', '张强');
commit;

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

转载于:http://blog.itpub.net/12712263/viewspace-606930/