ORACLE层次化查询

数据应用组

SQL> set pagesize 10000

SQL> select * from more_employees;

EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_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. 使用CONNECT BY和START WITH子句

SELECT语句中的CONNECT BY和START WITH子句的语法如下

SELECT [LEVEL],column,expression, ...

FROM table

[WHERE where_clause]

[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

其中：

• LEVEL是一个“伪列”，代表树的第几层。对根节点来说，LEVEL返回1，根节点的子节点返回2，以此类推。
• start_condition定义了层次化查询的起点。当编写层次化查询的时候必须指定START WITH子句。例如，可以将start_condition定义为employee=1，表示从员工#1开始。
• prior_condition 定义了父行和子行的关系。当编写层次化查询时必须定义CONNECT BY PRIOR子句。例如，可以将prior_condition定义为employee_id=manager_id，表示父节点的employee_id 和子节点的manager_id之间存在关系，也就是说，子节点的manager_id指向父节点的employee_id。

如下所示

SQL> select employee_id,manager_id,first_name,last_name

  2  from more_employees

  3  START WITH employee_id=100

  4  CONNECT BY PRIOR employee_id=manager_id;

2.使用伪列LEVEL

SQL> select LEVEL,employee_id,manager_id,first_name,last_name

from more_employees

START WITH employee_id=100

CONNECT BY PRIOR employee_id=manager_id

order by 1

下面这个查询使用COUNT()和LEVEL来获取数中的层次数

SQL> select count(distinct LEVEL)

  from more_employees

 START WITH employee_id = 100

CONNECT BY PRIOR employee_id = manager_id

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

可以用 LEVEL和LPAD函数对层次化查询结果进行格式化处理，方法是在数据的左边填补字符。如下例所示，根据不同LEVEL填充不同个数的空格，从而缩进显示员工的名字。

select LEVEL,

       LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name as employee

  from more_employees

 START WITH employee_id = 100

CONNECT BY PRIOR employee_id = manager_id

Order by 1;

4.从非根节点开始遍历

对树进行遍历不一定要从根节点开始；使用START WITH子句可以从任何节点开始。下面这个查询就是从Susan Jones开始；注意，Susan Jones的LEVEL返回1，Jane Brown的LEVEL返回2，以此类推。

select LEVEL,

       LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name as employee

  from more_employees

 START WITH last_name = 'Greenberg'

CONNECT BY