58.Oracle数据库SQL开发之 高级查询——层次化查询

58.Oracle数据库SQL开发之 高级查询——层次化查询

        欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/49847021

 先执行如下：

store@PDB1> select * from more_employees;

 

EMPLOYEE_ID MANAGER_ID FIRST_NAMELAST_NAME  TITLE                SALARY

----------- ---------- -------------------- -------------------- ----------

           1         James           Smith     CEO                         800000

           2       1 Ron        Johnson   Sales Manager              600000

           3       2 Fred       Hobbs     Sales Person        200000

           4       1 Susan    Jones     Support Manager         500000

           5       2 Rob        Green     Sales Person         40000

           6       4 Jane      Brown     Support Person             45000

           7       4 John      Grey          Support Manager         30000

           8       7 Jean      Blue           Support Person             29000

           9       6 Henry    Heyson    Support Person             30000

          10       1 Kevin     Black     Ops Manager        100000

          11      10 Keith     Long          Ops Person          50000

          12      10 Frank     Howard    Ops Person        45000

          13      10 Doreen          Penn          Ops Person          47000

 

13 rows selected.

员工关系如下图1：

1.  使用CONNECT BY和START WITH子句

SELECT语句的CONNECT BY和START WITH子句的语法如下所示：

SELECT [LEVEL]，column,expression,…

         FROM table

         [WHERE where_clause]

         [[START WITH start_condition] [CONNECTBY PRIOR prior_condition]];

其中，LEVEL是一个 伪列

Start_condition定义了层次化查询的起点。

Prior_condition定义了父行和子行之间的关系。编写层次化查询时必须定义CONNECT BY PRIOR子句。

例如执行：

store@PDB1>select employee_id,manager_id,first_name,last_name from more_employees startwith employee_id = 1 connect by prior employee_id=manager_id;

 

EMPLOYEE_IDMANAGER_ID FIRST_NAME LAST_NAME

--------------------- ---------- ----------

          1         James           Smith

          2       1 Ron        Johnson

          3       2 Fred       Hobbs

          5       2 Rob        Green

          4       1 Susan    Jones

          6       4 Jane      Brown

          9       6 Henry    Heyson

          7       4 John      Grey

          8       7 Jean      Blue

          10      1 Kevin     Black

          11      10 Keith     Long

          12      10 Frank     Howard

          13      10 Doreen          Penn

 

13 rowsselected.

2.  使用伪列LEVEL

使用伪列LEVEL显示节点在树中的层次：

store@PDB1> selectlevel,employee_id,manager_id,first_name,last_name from more_employees startwith employee_id=1 connect by prior employee_id=manager_id order by level;

 

    LEVEL EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME

---------- ----------- -------------------- ----------

          1          1                James     Smith

          2         10              1Kevin      Black

          2          2              1Ron             Johnson

          2          4              1Susan      Jones

          3         13            10 Doreen     Penn

          3          7              4John            Grey

          3         11            10 Keith      Long

          3          5              2Rob             Green

          3          3              2Fred            Hobbs

          3         12            10 Frank      Howard

          3         6              4Jane            Brown

          4          8              7Jean            Blue

          4          9              6Henry      Heyson

 

13 rows selected.

使用COUNT函数和LEVEL来获得树中的层次数

store@PDB1> select count(distinct level) frommore_employees start with employee_id = 1 connect by prioremployee_id=manager_id;

 

COUNT(DISTINCTLEVEL)

--------------------

                      4

3.  格式化层次化查询的结果

可以用LEVEL和LPAD函数对层次化查询结果进行格式化处理，方法是在数据的左边填充字符。

store@PDB1> set pagesize 999

store@PDB1> column employee format a25

store@PDB1> select level,

    lpad('',2*level-1)||first_name||' '||last_name as employee from more_employees

    start withemployee_id=1 connect by prior employee_id = manager_id;

    LEVEL EMPLOYEE

---------- -------------------------

          1  JamesSmith

          2    RonJohnson

          3     FredHobbs

          3     RobGreen

          2   Susan Jones

          3     JaneBrown

          4       Henry Heyson

          3     JohnGrey

          4       Jean Blue

          2   Kevin Black

          3     KeithLong

          3     FrankHoward

          3     DoreenPenn

13 rows selected.

4.  从非根节点开始遍历

执行如下：

select level,

    lpad('',2*level-1)||first_name||' '||last_name as employee from more_employees

        startwith last_name=

    'Jones'connect by prior employee_id=manager_id;

    LEVEL EMPLOYEE

---------- -------------------------

          1  SusanJones

          2   Jane Brown

          3     HenryHeyson

          2   John Grey

          3     JeanBlue

5.  在START WITH子句中使用子查询

在START WITH子句中可以使用子查询。

store@PDB1> select level,

        lpad('',2*level-1)||first_name||' '||last_name as employee from more_employees

    start withemployee_id=(

    selectemployee_id from more_employees

    wherefirst_name='Kevin'

    andlast_name='Black')

        connectby prior employee_id=manager_id;

    LEVEL EMPLOYEE

---------- -------------------------

          1  KevinBlack

          2   Keith Long

          2   Frank Howard

          2   Doreen Penn

6.  从下向上遍历树

不一定非要按照从父节点到子节点的顺序从上至下遍历树。

也可以从给某个子节点开始，自下而上遍历。实现的方法是交换父节点与子节点在CONNECT BY PRIOR子句中的顺序。

执行如下：

select level,

        lpad('',2*level-1)||first_name||' '||last_name as employee from more_employees

    start withlast_name=

'Blue'connect by prior manager_id=employee_id;

    LEVEL EMPLOYEE

---------- -------------------------

          1  JeanBlue

          2   John Grey

          3        Susan Jones

          4         JamesSmith

7.  从层次查询中删除节点和分支

select level,

        lpad('',2*level-1)||first_name||' '||last_name as employee from more_employees

        wherelast_name !=

    'Johnson'start with employee_id=1 connect by prior employee_id=manager_id;

 

    LEVEL EMPLOYEE

---------- -------------------------

          1  JamesSmith

          3     FredHobbs

          3     RobGreen

          2   Susan Jones

          3     JaneBrown

          4       Henry Heyson

          3     JohnGrey

          4       Jean Blue

          2   Kevin Black

          3     KeithLong

          3     FrankHoward

          3     DoreenPenn

12 rows selected.

为了将整个分支都从查询结果中除去，可以再CONNECT BY PRIOR子句中使用AND子句。

select level,lpad(' ',2*level-1)||first_name||''||last_name as employee from more_employees

   start withemployee_id=1 connect by prior employee_id = manager_id

        andlast_name != 'Johnson';

 

    LEVEL EMPLOYEE

---------- -------------------------

          1  JamesSmith

          2   Susan Jones

          3     JaneBrown

          4       Henry Heyson

          3     JohnGrey

          4       Jean Blue

          2   Kevin Black

          3     KeithLong

          3     FrankHoward

          3     DoreenPenn

 

10 rows selected.

8.  在层次化查询中加入其它条件

使用WHERE子句可以再层次化查询中加入其它条件。

执行如下：

store@PDB1> select level,lpad('',2*level-1)||first_name||' '||last_name as employee,salary from more_employeeswhere salary <= 50000 start with employee_id = 1 connect by  prior employee_id = manager_id;

 

    LEVEL EMPLOYEE                     SALARY

---------- -----------------------------------

          3     RobGreen                   40000

          3     JaneBrown                 45000

          4       Henry Heyson                  30000

          3     JohnGrey                     30000

          4       Jean Blue                 29000

          3     KeithLong                    50000

          3     FrankHoward             45000

          3     DoreenPenn               47000

 

8 rows selected.