(Les18 Hierarchical Retrieving)[20180105]

读书目的

    -领会分层查询概念

    -创建树形协会报告

    -格式化分层数据

    -树形协会排除分支

 

    分层查询

        语法

 SELECT [LEVEL],<column>,exper ...
        FROM <table_name>
        [WHERE condition(s)]
        START WITH condition(s)
        CONNECT BY PRIOR condition(s);

 

LEVEL关键字重回层次查询中的层级数

START WITH 指定层级初始的第一流

CONNECT BY PRIOR 指定父行和子行之间的涉及列,此子句是层次查询必需的

 

        知识点

Oracle中start with…connect by prior子句用法 

connect by 是结构化查询中用到的,其基本语法是: 

select … from tablename start with 条件1 

connect by 条件2 

where 条件3;

例:

select * from table

start with org_id = ‘HBHqfWGWPy’

connect by prior org_id = parent_id;

简易说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段: 

org_id,parent_id那么通过代表每一条记下的parent是何人,就足以形成一个树状结构。 

用上述语法的询问可以取得这棵树的有所记录。 

其中:

规则1
是根结点的范围语句,当然可以放宽限制标准,以博得六个根结点,实际就是多棵树。 

标准化2 是连连条件,其中用PRIOR表示上一条记下,比如 CONNECT BY PRIOR
org_id = parent_id就是说上一条记下的org_id
是本条记下的parent_id,即本记录的老爹是上一条记下。 

规格3 是过滤条件,用于对回到的拥有记录举办过滤。

一句话来说介绍如下:

早扫描树结构表时,需要依此访问树结构的每个节点,一个节点只好访问三回,其访问的手续如下:

首先步:从根节点起先;

第二步:访问该节点;

其三步:判断该节点有无未被访问的子节点,若有,则转向它最左边的未被访问的子节,并执行第二步,否则执行第四步;

第四步:若该节点为根节点,则做客停止,否则执行第五步;

第五步:重回到该节点的父节点,并施行第三步骤。

 

总的说来:扫描整个树结构的过程也即是中序遍历树的进程。

 

1. 树结构的讲述

树结构的多少存放在表中,数据里面的层次关系即父子关系,通过表中的列与列间的关联来叙述,如EMP表中的EMPNO和MGR。EMPNO表示该雇员的数码,MGR表示负责人该雇员的人的编号,即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个代表父节点的MGR(除根节点外),通过各样节点的父节点,就可以规定整个树结构。

在SELECT命令中应用CONNECT BY 和蔼START WITH
子句可以查询表中的树型结构涉及。其命令格式如下:

SELECT 。。。

CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 裂名2}

[START WITH];

中间:CONNECT
BY子句表达每行数据将是按层次顺序检索,并规定将表中的数额连入树型结构的涉嫌中。PRIORY运算符必须放置在连续关系的两列中某一个的面前。对于节点间的父子关系,PRIOR运算符在边上表示父节点,在另一侧表示子节点,从而确定查找树结构是的逐条是自顶向下或者自底向上。在连接关系中,除了可以应用列名外,还同意利用列表明式。START
WITH
子句为可选用,用来标识哪个节点作为查找树型结构的根节点。若该子句被概括,则象征拥有满意查询条件的行作为根节点。

START WITH: 不但可以指定一个根节点,还是可以够指定两个根节点。

2. 关于PRIOR

运算符PRIOR被停放于等号前后的地方,决定着询问时的摸索顺序。

PRIOR被放到CONNECT
BY子句中等号的前方时,则强制从根节点到叶节点的顺序检索,即由父节点向子节点方向通过树结构,我们称为自顶向下的法子。如:

CONNECT BY PRIOR EMPNO=MGR

PIROR运算符被放到CONNECT BY
子句中等号的后面时,则强制从叶节点到根节点的次第检索,即由子节点向父节点方向通过树结构,我们誉为自底向上的艺术。例如:

CONNECT BY EMPNO=PRIOR MGR

在那种办法中也应指定一个先河的节点。

3. 定义查找起头节点

在自顶向下询问树结构时,不但可以从根节点伊始,还可以定义任何节点为起始节点,以此开首向下搜寻。这样查找的结果就是以该节点为起先的结构树的一枝。

4.使用LEVEL

在富有树结构的表中,每一行数据都是树结构中的一个节点,由于节点所处的层次地点不同,所以每行记录都得以有一个层号。层号遵照节点与根节点的相距确定。不论从哪个节点起初,该起初根节点的层号始终为1,根节点的子节点为2,
依此类推。图1.2就意味着了树结构的层系。

5.节点和支行的剪裁

在对树结构举办查询时,可以去掉表中的某些行,也得以剪掉树中的一个拨出,使用WHERE子句来限定树型结构中的单个节点,以去掉树中的单个节点,但它却不影响其子孙节点(自顶向下检索时)或前辈节点(自底向顶检索时)。

6.排序展现

象在任何查询中平等,在树结构查询中也得以运用ORDER BY
子句,改变查询结果的显得顺序,而不必依据遍历树结构的各类。

 

 

例子:

 HR@cqoraxt0>select last_name,employee_id,manager_id,level from employees
start with employee_id =100
connect by prior employee_id=manager_id ;
LAST_NAME            EMPLOYEE_ID MANAGER_ID      LEVEL
-------------------- ----------- ---------- ----------
Dereck                       100          1          1
Gary                         200        100          2
Gaga                         203        200          3
Wendy                        204        200          3
Xi                           205        200          3
Tinan                        206        200          3
Ling                         207        200          3
Klec                         300        100          2
Owen                         301        300          3
Yi                           302        300          3

注意:connect by prior链接顺序

HR@cqoraxt0>
select last_name,employee_id,manager_id,level from employees
start with employee_id =302
connect by prior manager_id=employee_id
order siblings by last_name;
LAST_NAME            EMPLOYEE_ID MANAGER_ID      LEVEL
-------------------- ----------- ---------- ----------
Yi                           302        300          1
Klec                         300        100          2
Dereck                       100          1          3

 

    格式化层次查询

        实现目的,采纳层次查询自动缩进

          

HR@cqoraxt0>col last_name format a20
select lpad(last_name,length(last_name)+level,' ') last_name,employee_id,manager_id from employees
start with employee_id =100
connect by prior employee_id=manager_id;

LAST_NAME            EMPLOYEE_ID MANAGER_ID
-------------------- ----------- ----------
Dereck                      100          1
  Gary                       200        100
   Gaga                      203        200
   Wendy                     204        200
   Xi                        205        200
   Tinan                     206        200
   Ling                      207        200
  Klec                       300        100
   Owen                      301        300
   Yi                        302        300

HR@cqoraxt0>select lpad(last_name,length(last_name)+level,'-') last_name,employee_id,manager_id from employees
start with employee_id =100
connect by prior employee_id=manager_id;

LAST_NAME            EMPLOYEE_ID MANAGER_ID
-------------------- ----------- ----------
-Dereck                      100          1
--Gary                       200        100
---Gaga                      203        200
---Wendy                     204        200
---Xi                        205        200
---Tinan                     206        200
---Ling                      207        200
--Klec                       300        100
---Owen                      301        300
---Yi                        302        300
10 rows selected.

    排除分支

            方式一:

                    WHERE condition –排除指定分支单个节点

                     START WITH

                      CONNECT BY PRIOR

            方式二:

                    START WITH

                    CONNECT BY PRIOR

                    AND condition –排除分支

            注意:情势一只会免去单个节点,它下面的依附分支不会去掉。   

 

            例子一

            

HR@cqoraxt0>
select last_name,employee_id,manager_id,level from employees
where employee_id !=300
start with employee_id =100
connect by prior employee_id=manager_id
order siblings by last_name;

LAST_NAME            EMPLOYEE_ID MANAGER_ID      LEVEL
-------------------- ----------- ---------- ----------
Dereck                       100          1          1
Gary                         200        100          2
Gaga                         203        200          3
Ling                         207        200          3
Tinan                        206        200          3
Wendy                        204        200          3
Xi                           205        200          3
Owen                         301        300          3
Yi                           302        300          3
9 rows selected.

        例子二

         

 

   HR@cqoraxt0>select last_name,employee_id,manager_id,level from employees
            start with employee_id =100
            connect by prior employee_id=manager_id
            and employee_id !=300
            order siblings by last_name;

LAST_NAME            EMPLOYEE_ID MANAGER_ID      LEVEL
-------------------- ----------- ---------- ----------
Dereck                       100          1          1
Gary                         200        100          2
Gaga                         203        200          3
Ling                         207        200          3
Tinan                        206        200          3
Wendy                        204        200          3
Xi                           205        200          3

7 rows selected.

学学总括:

        1.层次查询概念和行使方法

        2.层次查询start with、connect by prior子句和level伪列含义

        3.经过level伪列对层次查询数据开展格式化

        4.排除层次查询单个节点和支行

 

相关文章