转载于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、 构建测试表与插入测试语句
- 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 17 38 2 9 17 3 9
8 17 3 8
15 38 2 10 15 3 10
5 15 3 5
3 5 4 5
2 5 4 5
6 38 2 26 1 13 26 2 12 26 2 1 26 2 18 1 11 18 2 7 18 2
已选择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.itpub.net/26844646/viewspace-749061/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26844646/viewspace-749061/