层次查询.doc

1、5.层次查询(Hierarical Retrival)5.1 基本概念对于层次查询需要掌握：1.解释层次查询的基本概念，识别需求中需要用到层次查询的能力。2.建立和格式化一个树形报表(tree report)。3.修剪树形结构的节点(node) 和枝(branches) 。关键词：tree,root(根),node,leaf(叶子),branch(树枝，分支)本节例子来源于表 s_emp,表结构和数据如下：看上面的表 s_emp,使用层次查询，我们可以获得一张表基于层次关系的数据集合。Oracle 是一种关系型数据库，在表中不可能以层次的关系存放数据。但是我们可以通过一定的规则，使用 tree

2、 walking(树的遍历或树的查找)来获得层次关系的数据。Hierarical query 是一种获得树的层析关系报表的方法。树形结构的数据集合，存在于我们日常生活中的很多地方，比如考虑一个家族关系，有长辈，长辈下面有子女，子女下面还可以有子女，这转化为层次或等级关系就是：根节点只有一个，下面有子节点，子节点下面还有子节点，这样组成了一棵树。(有时候，根节点 root 不一定只有一个，严格意义上说，这种情况不是一个严格的树)。当一种层次关系是存在一个表的行中，那么这种层次是可以获得的。例如，我们看 s_emp 表，对于 title:VP，我们知道这些行中都包含 manager_id=1,也就

3、是说，这些行属于 id=1 的雇员的下属雇员，那么有 title=vp 又可以获得一系列的层次，而这些层次的跟则是 id=1 这个雇员。由此，得到一棵树形结构数据集合。层次树或等级树，在譬如 家族关系，育种关系，组织管理，产品装配，人类进化，科学研究 等领广泛应用。下面我们就根据 s_emp 这张表，根据职位大小来描述一个树形结构图。如图：V e l a s q u e z （ i d = 1 t i t l e = p r e s i d e n t ） - - - - - r o o t l e v e l = 1N g a oN a g a y a m aQ u i c k - T o

4、- S e eR o p e b u r nC h i l d l e v e l = 2U r g u h a r tM e n c h uB i r i C a t c h p o l eH a v e lD a n c sS c h w a r t zM a g e e G i l j u m S e d e g h i N g u y e nL e v e l = 3L e v e l = 4只显示部分树形结构树形结构的父子关系，你可以控制：1. 遍历树的方向，是自上而下，还是自下而上。2. 确定层次的开始点(root)的位置。层次查询语句正是从这两个方面来确定的,start with

5、 确定开始点,connect by 确定遍历的方向。5.2 层次查询5.2.1 语法：层次查询是通过 start with 和 connect by 子句标识的。1.其中 level 关键字是可选的，表示等级，表示 root,2 表示 root 的 child,其他相同的规则。2.From 之后可以是 table,view 但是只能是一个 table。3.Where 条件限制了查询返回的行，但是不影响层次关系，属于将节点截断，但是这个被截断的节点的下层 child 不受影响。4.Start with 是表示开始节点，对于一个真实的层次关系，必须要有这个子句，但是不是必须的，后面详细介绍。5.c

6、onnect by prior 是指定父子关系，其中 prior 的位置不一定要在 connect by 之后，对于一个真实的层次关系，这也是必须的。对于 from 是视图的，那么这个 view 不能包含 join。5.2.2 遍历树Start with 子句：首先必须确定 startpoint，通过 start with 子句，后面加条件，这个条件是任何合法的条件表达式。Start with 确定将哪行作为 root，如果没有 start with,则每行都当作 root，然后查找其后代，这不是一个真实的查询。Start with 后面可以使用子查询 ，如果有 where 条件，则会截断层次

7、中的相关满足条件的节点，但是不影响整个层次结构。可以带多个条件。对于 s_emp,从 root title=president 开始，语句如下：select level,id,manager_id,last_name,title from s_empstart with manager_id is nullconnect by prior id=manager_id;这个语句与下面的语句等价，将 start with 后面的条件换成子查询。select level,id,manager_id,last_name,title from s_empstart with title=(select

8、title from s_emp where manager_id is null)connect by prior id=manager_id;注意：start with 和 connect by prior 不是 ANSI 标准 sql。Connect by 子句：Connect by 与 prior 确定一个层次查询的条件和遍历的方向(prior 确定) 。Connect by prior column_1=column_2;(其中 prior 表示前一个节点的意思，可以在 connect by 等号的前后，列之前，也可以放到 select 中的列之前)。Connect by 也可以带多

9、个条件，比如 connect by prior id=manager_id and id10;方向：1. 自顶向下遍历：就是先由根节点，然后遍历子节点。column_1 表示父key,column_2 表示子 key。即这种情况下：connect by prior 父 key=子 key 表示自顶向下，等同于 connect by 子 key=prior 父 key.例如：select level,id,manager_id,last_name, title from s_empstart with manager_id=2connect by id=prior manager_id;-自下而

10、上遍历2. 自底向上遍历：就是先由最底层的子节点，遍历一直找到根节点。与上面的相反。Connect by 之后不能有子查询，但是可以加其他条件,比如加上 and id !=2 等。这句话则会截断树枝，如果 id=2 的这个节点下面有很多子孙后代，则全部截断不显示。比如下面的句子：select level,id,manager_id,last_name,title from s_empstart with title=(select title from s_emp where manager_id is null)connect by prior id=manager_id and id!=2

11、;不来不加上 id!=2，共有 25 条记录，现在加上这个条件只有 9 条记录了，因为 id=2 的后代包括自己共有 16 条记录，全部被截断。5.2.3 使用 level 和 lpad 格式化报表Level 是层次查询的一个伪列，如果有 level，必须有 connect by,start with 可以没有。Lpad 是在一个 string 的左边添加一定长度的字符，并且满足中间的参数长度要求，不满足自动添加。例如现在的需求是，输出 s_emp 等级报表，root 节点的 last_name 不变，比如第2 等级，也就是 level=2 的前面加两个 _符号，level=3 的前面加 4

12、个。这样我们可以得到一个公式就是：Lpad(last_name,length(last_name)+(level*2)-1,_)可以得出下面的语句：select level,id,manager_id,lpad(last_name,length(last_name)+(level*2)-2,_),title,prior last_name from s_empstart with manager_id is nullconnect by prior id=manager_id;select 中的 prior last_name 是输出其父亲的 last_name.这个语句执行的结果如下：5.2

13、.4 修剪 branches上面已经提到，where 子句会将节点删除，但是其后代不会受到影响，connect by 中加上条件会将满足条件的整个树枝包括后代都删除。要注意，如果是 connect by 之后加条件正好条件选到根，那么结果和没有加一样，如图所示：5.2.5 层次查询限制1.层次查询 from 之后如果是 table，只能是一个 table，不能有 join。2.from 之后如果是 view，则 view 不能是带 join 的。3.使用 order by 子句，order 子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注 level，获得某行在层

14、次中的深度，但是这两种都会破坏层次。见 5.3 增强特性中的使用 siblings 排序。4.在 start with 中表达式可以有子查询，但是 connect by 中不能有子查询。5.2.6 应用1）查询每个等级上节点的数目先查看总共有几个等级：select count(distinct level)from s_empstart with manager_id is nullconnect by prior id=manager_id;要查看每个等级上有多少个节点，只要按等级分组，并统计节点的数目即可，可以这样写：select level,count(last_name)from s_

15、empstart with manager_id is nullconnect by prior id=manager_idgroup by level;2）格式化报表见 5.2.3.3）查看等级关系有一个常见的需求，比如给定一个具体的 emp 看是否对某个 emp 有管理权，也就是从给定的节点寻找，看其子树节点中能否找到这个节点。如果找到，返回，找不到，no rows returned.比如对于 s_emp 表，从根节点，也就是 manager_id is null 的开始找，看 first_name= Elena是否被它管理，语句如下：select level,a.* from s_emp

16、 awhere first_name=Elena 被管理的节点start with manager_id is null 开始节点connect by prior id=manager_id;4）删除子树比如有这样的需求，现在要裁员，将某个部门的员工包括经理全部裁掉，那么可以使用树形查询作为子查询实现这个功能。将 id 为 2 的员工管理的所有员工包括自己删除。因为要全部裁掉了。那么语句如下：delete from s_emp where id in(select id from s_emp astart with id=2 从id=2的员工开始查找其子节点，把整棵树删除connect by

17、prior id=manager_id);5）找出每个部门的经理这个需求，我们可以从树中查找，也就是对于每个部门选最高等级节点。可以使用connect by 后加条件过滤 branches 的方法。select level,a.* from s_emp astart with manager_id is nullconnect by prior id=manager_id and dept_id !=prior dept_id;-当前行的 dept_id 不等于前一行的dept_id，即每个子树中选最高等级节点6）查询一个组织中最高的几个等级用 where level 条件过滤select l

18、evel,a.* from s_emp awhere level 展现。如root,则是 =root_last_name, level=2 的就是=root_last_name=level_2_last_name, 并且利用lpad 格式化报表。语句是：select last_name,level,id,lpad( , level * 2 - 1) | sys_connect_by_path(last_name, =) 前面按层次加空格，-并且后面加上路径from s_empstart with manager_id is nullconnect by prior id = manager_i

19、d;结果如图所示：下面的是 oracle10g 新增特性5.3.2 CONNECT_BY_ISLEAF在 oracle9i 的时候，查找指定 root 下的叶子节点，是很复杂的，oracle10g 引入了一个新的函数，connect_by_isleaf,如果行的值为 0 表示不是叶子节点，1 表示是叶子节点。找出 s_emp 中找出 manager_id=2 开始的行为 root，表示叶子节点和非叶子节点，那么语句如下：select level,id,manager_id,last_name,title,(case -使用 case表达式判断是否是叶子节点when connect_by_isl

20、eaf = 1 then叶子else不是叶子end) isleaffrom s_empstart with manager_id = 2connect by prior id = manager_id;5.3.3 CONNECT_BY_ISCYCLE 和 NOCYCLE 关键字如果从 root 节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，oracle 会报 ORA-01436: CONNECT BY loop in user data，在 9i 中只能将发生死循环的不加入到树中或删除，在 10g 中可以用 nocycle 关键字加在 connect by 之后，避免循

21、环的参加查询操作。并且通过 connect_by_iscycle 得到哪个节点发生循环。0 表示未发生循环，1 表示发生了循环，如：create table family1(fatherid number,childid number);insert into family1 values(null,1);insert into family1 values(1,2);-父节点为 1insert into family1 values(1,3);insert into family1 values(2,4);-发生循环insert into family1 values(4,1);-子节点为

22、1 insert into family1 values(4,5);commit;select connect_by_iscycle, fatherid,childid,sys_connect_by_path(childid,/)from family1start with fatherid is nullconnect by nocycle prior childid=fatherid;结果是：5.3.4 CONNECT_BY_ROOTOracle10g 新增 connect_by_root,用在列名之前表示此行的根节点的相同列名的值。如：select connect_by_root las

23、t_name root_last_name, connect_by_root id root_id,id,last_name,manager_idfrom s_empstart with manager_id is nullconnect by prior id=manager_id;结果为：5.3.5 使用 SIBLINGS 关键字排序前面说了，对于层次查询如果用 order by 排序，比如 order by last_name 则是先做完层次获得 level,然后按 last_name 排序，这样破坏了层次，比如特别关注某行的深度，按 level排序，也是会破坏层次的。在 oracle10g 中，增加了 siblings 关键字的排序。语法：order siblings by 它会保护层次，并且在每个等级中按 expre 排序。select level,