【SQL】CONNECT BY 层次化查询

层次化查询，顾名思义就是把查询结果有层次的呈现出来。层次化查询结果类似于树状结构，最顶端的是“根节点”，下面是“父节点”，没有子节点的是“叶节点”。一个公司的组织结构图就是很好的例子。

为了让一个或多个表具有层次关系，必须使用相关的字段将表关联起来。比如HR用户下的employees表中的employee_id和manager_id列。

基本语法为：

[START WITH condition1] CONNECT BY condition2

START WITH condition1:用来指定根节点，condition1中可以加子查询，所以满足condition1的结果都会被用作根节点。

CONNECT BY condition2：condition2中必须包含PRIOR关键字，用来指定列是来自父行。PRIOR可以位于比较操作符的任何一方。condition不能包含子查询。

如：

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

  2  from employees

  3  start with manager_id is null

  4  connect by prior employee_id=manager_id;

 

EMPLOYEE_ID MANAGER_ID FIRST_NAME           LAST_NAME                 HIRE_DATE

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

        100            Steven               King                      2003-06-17 00:00:00

        101        100 Neena                Kochhar                   2005-09-21 00:00:00

        108        101 Nancy                Greenberg                 2002-08-17 00:00:00

        109        108 Daniel               Faviet                    2002-08-16 00:00:00

        110        108 John                 Chen                      2005-09-28 00:00:00

        111        108 Ismael               Sciarra                   2005-09-30 00:00:00

        112        108 Jose Manuel          Urman                     2006-03-07 00:00:00

        113        108 Luis                 Popp                      2007-12-07 00:00:00

        200        101 Jennifer             Whalen                    2003-09-17 00:00:00

        203        101 Susan                Mavris                    2002-06-07 00:00:00

 

为了知道当前查询树状结构所处的层次，可以使用level伪列。加入level伪列后显示如下。

SQL> select level,lpad(' ',2*(level-1))||last_name "EmpName",hire_date,salary

  2  from employees

  3  start with manager_id is null

  4  connect by manager_id=prior employee_id;

 

     LEVEL EmpName         HIRE_DATE               SALARY

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

         1 King            2003-06-17 00:00:00      24000

         2   Kochhar       2005-09-21 00:00:00      17000

         3     Greenberg   2002-08-17 00:00:00      12008

         4       Faviet    2002-08-16 00:00:00       9000

         4       Chen      2005-09-28 00:00:00       8200

         4       Sciarra   2005-09-30 00:00:00       7700

         4       Urman     2006-03-07 00:00:00       7800

         4       Popp      2007-12-07 00:00:00       6900

         3     Whalen      2003-09-17 00:00:00       4400

         3     Mavris      2002-06-07 00:00:00       6500

         3     Baer        2002-06-07 00:00:00      10000

         3     Higgins     2002-06-07 00:00:00      1200