层次查询.docVIP

5.层次查询(Hierarical Retrival) 5.1 基本概念 对于层次查询需要掌握： 1.解释层次查询的基本概念，识别需求中需要用到层次查询的能力。 2.建立和格式化一个树形报表(tree report)。 3.修剪树形结构的节点(node)和枝(branches)。 关键词：tree,root(根),node,leaf(叶子),branch(树枝，分支) 本节例子来源于表s_emp,表结构和数据如下： 看上面的表s_emp,使用层次查询，我们可以获得一张表基于层次关系的数据集合。Oracle是一 种关系型数据库，在表中不可能以层次的关系存放数据。但是我们可以通过一定的规则，使用tree walking(树的遍历或树的查找)来获得层次关系的数据。Hierarical query是一种获得树的层析关系报表的方法。 树形结构的数据集合，存在于我们日常生活中的很多地方，比如考虑一个家族关系，有长辈，长辈下面有子女，子女下面还可以有子女，这转化为层次或等级关系就是：根节点只有一个，下面有子节点，子节点下面还有子节点，这样组成了一棵树。(有时候，根节点root不一定只有一个，严格意义上说，这种情况不是一个严格的树)。 当一种层次关系是存在一个表的行中，那么这种层次是可以获得的。例如，我们看s_emp表，对于title:VP，我们知道这些行中都包含manager_id=1,也就是说，这些行属于id=1的雇员的下属雇员，那么有title=vp又可以获得一系列的层次，而这些层次的跟则是id=1这个雇员。由此，得到一棵树形结构数据集合。 层次树或等级树，在譬如家族关系，育种关系，组织管理，产品装配，人类进化，科学研究等领 广泛应用。 下面我们就根据s_emp这张表，根据职位大小来描述一个树形结构图。如图： 只显示部分树形结构 树形结构的父子关系，你可以控制： 遍历树的方向，是自上而下，还是自下而上。 确定层次的开始点(root)的位置。 层次查询语句正是从这两个方面来确定的,start with确定开始点,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.connect 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条件，则会截断层次中的相关满足条件的节点，但是不影响整个层次结构。可以带多个条件。 对于s_emp,从root title=president开始，语句如下： select level,id,manager_id,last_name,title from s_emp start with manager_id is null connect by prior id=manager_id; 这个语句与下面的语句等价，将start with 后面的条件换成子查询。 select level,id,manager_id,last_name,title from s_emp start with title=(select 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确定一个层次查询的条件和遍历的方向(prio