转载于http://blog.csdn.net/nsj820/article/details/6299276
层次查询的概念

语法格式：

select [level], column, expr... from table
　　[where condition]
　　start with condition
　　connect by [prior column1= column2 |
　　column1 = prior column2];

层次查询是通过start with和connect by子句标识的：

1.其中level关键字是可选的，表示等级，1表示root,2表示root的child,其他相同的规则。

2.From之后可以是table,view但是只能是一个table。

3.Where条件限制了查询返回的行，但是不影响层次关系，属于将节点截断，但是这个被截断的节点的下层child不受影响。

4.Start with是表示开始节点，对于一个真实的层次关系，必须要有这个子句，但是不是必须的。

5.connect by prior是指定父子关系，其中prior的位置不一定要在connect by之后，对于一个真实的层次关系，这也是必须的。

对于from是视图的，那么这个view不能包含join。

层次查询限制

1.层次查询from 之后如果是table，只能是一个table，不能有join。

2.from之后如果是view，则view不能是带join的。

3.使用order by子句，order子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注level，获得某行在层次中的深度，但是这两种都会破坏层次。见增强特性中的使用siblings排序。

4.在start with中表达式可以有子查询，但是connect by中不能有子查询。

层次查询的增强特性

1、SYS_CONNECT_BY_PATH

Oracle 9i提供了sys_connect_by_path(column,char),其中column是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

下面的是oracle10g新增特性

2、 CONNECT_BY_ISLEAF

在oracle9i的时候，查找指定root下的叶子节点，是很复杂的，oracle10g引入了一个新的函数，connect_by_isleaf,如果行的值为0表示不是叶子节点，1表示是叶子节点。

3、CONNECT_BY_ISCYCLE和NOCYCLE关键字

如果从root节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，oracle会报ORA-01436: CONNECT BY loop in user data，在9i中只能将发生死循环的不加入到树中或删除，在10g中可以用nocycle关键字加在connect by之后，避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环。0表示未发生循环，1表示发生了循环。

4、CONNECT_BY_ROOT

Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。

5、使用SIBLINGS关键字排序

对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name排序，这样破坏了层次，比如特别关注某行的深度，按level排序，也是会破坏层次的。

在oracle10g中，增加了siblings关键字的排序。

语法：order siblings by

它会保护层次，并且在每个等级中按expre排序。

示例：

1、 构建测试表与插入测试语句

[c-sharp] view plain copy print ?

create table tab_connect_by (child number,parent number);
insert into tab_connect_by (CHILD, PARENT) values(2, 5);
insert into tab_connect_by (CHILD, PARENT) values(3, 5);
insert into tab_connect_by (CHILD, PARENT) values(10, 15);
insert into tab_connect_by (CHILD, PARENT) values(5, 15);
insert into tab_connect_by (CHILD, PARENT) values(9, 17);
insert into tab_connect_by (CHILD, PARENT) values(8, 17);
insert into tab_connect_by (CHILD, PARENT) values(15, 38);
insert into tab_connect_by (CHILD, PARENT) values(17, 38);
insert into tab_connect_by (CHILD, PARENT) values(6, 38);
insert into tab_connect_by (CHILD, PARENT) values(13, 26);
insert into tab_connect_by (CHILD, PARENT) values(1, 26);
insert into tab_connect_by (CHILD, PARENT) values(12, 26);
insert into tab_connect_by (CHILD, PARENT) values(11, 18);
insert into tab_connect_by (CHILD, PARENT) values(7, 18);
insert into tab_connect_by (CHILD, PARENT) values(38, null);
insert into tab_connect_by (CHILD, PARENT) values(26, null);
insert into tab_connect_by (CHILD, PARENT) values(18, null);
commit;

2、 查询语句1

select a.child,
a.parent,
level "层次",
sys_connect_by_path(child, '<-') "合并层次",
prior a.child "父节点",
connect_by_root a.child "根节点",
decode(connect_by_isleaf, 1, a.child, null) "子节点",
decode(connect_by_isleaf, 1, '是', '否') "是否子节点"
from tab_connect_by a
start with a.parent is null --从parent为空开始扫描
connect by prior a.child = a.parent --以child为父列连接parent
order siblings by child desc --对层次排序
;
     CHILD     PARENT       层次合并层次       父节点     根节点     子节点是
---------- ---------- ---------- ---------- ---------- ---------- ---------- --
        38                     1 <-38                          38            否
        17         38          2 <-38<-17           38         38            否
         9         17          3 <-38<-17<-         17         38          9 是
                                 9

         8         17          3 <-38<-17<-         17         38          8 是
                                 8

        15         38          2 <-38<-15           38         38            否
        10         15          3 <-38<-15<-         15         38         10 是
                                 10

         5         15          3 <-38<-15<-         15         38            否
                                 5

         3          5          4 <-38<-15<-          5         38          3 是
                                 5<-3

         2          5          4 <-38<-15<-          5         38          2 是
                                 5<-2

         6         38          2 <-38<-6            38         38          6 是
        26                     1 <-26                          26            否
        13         26          2 <-26<-13           26         26         13 是
        12         26          2 <-26<-12           26         26         12 是
         1         26          2 <-26<-1            26         26          1 是
        18                     1 <-18                          18            否
        11         18          2 <-18<-11           18         18         11 是
         7         18          2 <-18<-7            18         18          7 是

已选择17行。

3、 查询语句2

Select level as "层次", connect_by_iscycle,connect_by_isleaf,parent, child
　　From tab_connect_by
　　Connect by nocycle prior child = parent
　　Start with parent is null;      层次 CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF     PARENT      CHILD
---------- ------------------ ----------------- ---------- ----------
         1                  0                 0                    18
         2                  0                 1         18          7
         2                  0                 1         18         11
         1                  0                 0                    26
         2                  0                 1         26          1
         2                  0                 1         26         12
         2                  0                 1         26         13
         1                  0                 0                    38
         2                  0                 1         38          6
         2                  0                 0         38         15
         3                  0                 0         15          5
         4                  0                 1          5          2
         4                  0                 1          5          3
         3                  0                 1         15         10
         2                  0                 0         38         17
         3                  0                 1         17          8
         3                  0                 1         17          9

已选择17行。

练习（转自itpub）
2012-1-7 层次查询
作者：Kim Berg Hansen
难度：中

我创建了如下的表并填入数据:
create table plch_emp (
   empno       number(4)   primary key,
   ename       varchar2(10),
   mgr         number(4)   references plch_emp(empno)
);

insert into plch_emp values (7839, 'KING' , null);
insert into plch_emp values (7698, 'BLAKE' , 7839);
insert into plch_emp values (7499, 'ALLEN' , 7698);
insert into plch_emp values (7900, 'JAMES' , 7698);
insert into plch_emp values (7654, 'MARTIN', 7698);
insert into plch_emp values (7844, 'TURNER', 7698);
insert into plch_emp values (7521, 'WARD' , 7698);
insert into plch_emp values (7782, 'CLARK' , 7839);
insert into plch_emp values (7934, 'MILLER', 7782);
insert into plch_emp values (7566, 'JONES' , 7839);
insert into plch_emp values (7902, 'FORD' , 7566);
insert into plch_emp values (7369, 'SMITH' , 7902);
insert into plch_emp values (7788, 'SCOTT' , 7566);
insert into plch_emp values (7876, 'ADAMS' , 7788);

commit;

我老板要我写一个查询来显示所有那些“不是其他员工的经理”的员工，并且显示他或她的直接上级（在层次中往上一层的员工）的姓名，如果有上级的话。

哪些选项提供了满足要求的查询？换句话说，就上述数据而言，查询必须产生这样的输出：
PROGRAMMER      BOSS
--------------- ---------------
ADAMS           SCOTT
ALLEN           BLAKE
JAMES           BLAKE
MARTIN          BLAKE
MILLER          CLARK
SMITH           FORD
TURNER          BLAKE
WARD            BLAKE

注：列标题"PROGRAMMER（程序员）"是因为在我们公司，所有不是经理的人都是程序员。有点奇怪是吗？但我们程序员喜欢这样，挺好！

(A)
select
   subordinate.ename as programmer,
   manager.ename as boss
from plch_emp subordinate
join plch_emp manager
   on manager.empno = subordinate.mgr
where manager.mgr is not null
order by
   subordinate.ename;

(B)
select
   ename as programmer,
   (
      select ename
      from plch_emp boss
      where boss.empno = plch_emp.mgr
   ) as boss
from plch_emp
where empno not in (
   select mgr
   from plch_emp
)
order by
   ename;

(C)
select
   ename as programmer,
   (
      select ename
      from plch_emp boss
      where boss.empno = plch_emp.mgr
   ) as boss
from plch_emp
where not exists (
   select null
   from plch_emp subordinate
   where subordinate.mgr = plch_emp.empno
)
order by
   ename;

(D)
select
   ename as programmer,
   prior ename as boss
from plch_emp
where prior mgr is null
start with mgr is null
connect by mgr = prior empno
order by
   ename;

(E)
select
   ename as programmer,
   prior ename as boss
from plch_emp
where connect_by_isleaf = 1
start with mgr is null
connect by mgr = prior empno
order by
   ename;

2012-1-7 答案cE

A: where manager.mgr is not null这个条件错了，题目并没有要求经理的上级不为空。
B: where empno not in (
    select mgr
    from plch_emp
   ) 这个条件错了，题目并没有说当经理的员工不显示
d: 这个查询把有下级的员工也显示出来了。

ITPub博客

层次查询

转载于http://blog.csdn.net/nsj820/article/details/6299276
层次查询的概念

示例：

最新文章

ITPub博客

层次查询

转载于http://blog.csdn.net/nsj820/article/details/6299276层次查询的概念

示例：

最新文章

转载于http://blog.csdn.net/nsj820/article/details/6299276
层次查询的概念