【Oracle】 层级查询 Connect By

层级查询

Oracle 的层级查询原理类似于递归。所以有时也称之为递归查询。
主要运用于树形结构的查询，遍历指定节点的子节点或父节点。

基本用法

select * from table [start with condition1]
    connect by [prior] id=parentid;

/* from 后只能跟一个 table 或者 view */
/* where 条件限制查询返回的行数  不影响层级关系  将节点截断  但被截断的节点下层 child 不受影响 */
/* start with 开始节点 可以包含子查询 */
/* connect by 指定层级关系 */
/* prior 从语法来说 位于等号左 右 connect by 前后 都可以  等号左右的顺序决定层次方向 */
/* and 最后的 and 用来对分支进行选择 被截断的节点将整个分支（包含其 child ）从结果中去除 */

基本用法示例

以遍历上树形图为例：

SELECT LEVEL, T.ITEM, T.SUBITEM
  FROM (
  /* 构造树形图对应关系 start */
  SELECT 'A' ITEM, 'A1' SUBITEM
          FROM DUAL
        UNION
        SELECT 'A' ITEM, 'A2' SUBITEM
          FROM DUAL
        UNION
        SELECT 'A1' ITEM, 'A11' SUBITEM
          FROM DUAL
        UNION
        SELECT 'A2' ITEM, 'A21' SUBITEM
          FROM DUAL
        UNION
        SELECT 'A2' ITEM, 'A22' SUBITEM
          FROM DUAL
        UNION
        SELECT 'A22' ITEM, 'A221' SUBITEM
          FROM DUAL
        UNION
        SELECT 'A22' ITEM, 'A222' SUBITEM
          FROM DUAL
        UNION
        SELECT 'A22' ITEM, 'A223' SUBITEM FROM DUAL
        /* end */
        ) T
        START WITH T.ITEM = 'A'
CONNECT BY T.ITEM = PRIOR T.SUBITEM

其输出结果如下：

其中 START WITH 条件为开始遍历的节点。
如无该条件，默认遍历会所有树和子树。

扩展用法

因其类似于递归的原理。经常被用作构造虚列。最常用的就是构造日期列。
以日为例子（年月周同理）如下：

/* 获取最近 5 天的日期 */
SELECT SYSDATE - LEVEL + 1 FROM DUAL 
CONNECT BY LEVEL <= 5