Oracle 层次化查询(START WITH .. CONNECT BY)

• Oracle 提供了对于ANSI SQL的扩展来简化对树状层次结构的遍历。

  • START WITH … CONNECT BY子句。
  • PRIOR操作符。
  • LEVEL伪列。

• 层次结构的数据查询

  • START WITH condition1：指定层次结构中的根节点，所有满足condition1条件的都可以被考虑为根节点。如果没有指定STARTWITH子句，那么会使得所有的行都被考虑为根节点，这显然不符合树状查询的规律，也可以在condition1中包含子查询。
  • CONNECT BY condition2：指定层次结构中父行和子行之间的关系，这个关系是一个比较表达式，用来从当前的行开始比较其父行的列的相应值。condition2必须包含PRIOR操作符，用于指定列是来自父行，condition2不能包含子查询。
  • PRIOR是Oracle内置的操作符，仅用来处理层次化的查询。在PRIOR关键字之后的表达式将被作为查询中当前列的父列进行计算。

    [[START WITH condition1] CONNECT BY condition2]
    

    SELECT employee_id, manager_id, first_name, last_name, hire_date, salary FROM employees
    --表示根节点为manger_id 
    START WITH manager_id IS NULL
    --PRIOR表示父行的employee_id，等于当前行的manager_id 
    CONNECT BY manager_id = PRIOR employee_id;
    

• 使用LEVEL伪列

  • 如果在查询的过程中想知道当前查询树状结构所处的层次，可以使用LEVEL伪列，该列用来表示查询层次整型值。

    SELECT LEVEL, employee_id, manager_id, first_name, last_name, hire_date, salary FROM employees
    --表示根节点为manger_id 
    START WITH manager_id IS NULL
    --PRIOR表示父行的employee_id，等于当前行的manager_id 
    CONNECT BY manager_id = PRIOR employee_id;