Oracle SQL小示例系列-connect by prior

码蚁在线 2017-06-29

1066

对于oracle中使用connect by…prior实现树查询两种方式：自顶而下和自低而上，总是出现混淆状态，经常通过脚本来将prior放在等号前后来查看效果来分清使用的是哪种方法，因此对理清connect by…prior的树查询进行整理。

认识connect by prior

1.connect by prior基本语法

select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>;

<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。
<Conditional-2>：查询结果重起始根结点的限定条件。
<Conditional-3>：连接条件，其中用prior表示上一条记录，比如connect by prior id=praentid就是说上一条记录的id是本条记录的praentid，即本记录的父亲是上一条记录。

2.connect by prior递归理解：（以自顶而下举例）

2.1.level关键字理解

由于在分解connect by prior实现中需要借助level关键字，因此简单说明下level的含义：
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定

脚本：

SELECT t.*,level FROM EMP t CONNECT BY MGR= PRIOR EMPNO START WITH EMPNO=7839 order by level;

效果：

图一

2.2.使用子查询语句来分解递归

--1.执行初始话子查询来生成查询子表

SELECT * FROM EMP WHERE EMPNO=7839;

与图1中的level=1的值一致。

--2.通过第一步中的查询子表来执行递归子查询
SELECT B.*
FROM (SELECT EMPNO,MGR FROM EMP WHERE EMPNO=7839) A,EMP B
WHERE A.EMPNO=B.MGR;

与图1中的level=2的值一致。

--3.通过第二步中的查询子表来执行递归子查询
SELECT B.*
FROM ( SELECT B.EMPNO,B.MGR
FROM (SELECT EMPNO,MGR FROM EMP WHERE EMPNO=7839) A,EMP B
WHERE A.EMPNO=B.MGR) A,EMP B
WHERE A.EMPNO=B.MGR;

与图1中的level=3的值一致。

--4.通过第三步中的查询子表来执行递归子查询
SELECT B.*
FROM (SELECT B.EMPNO,B.MGR
      FROM (SELECT B.EMPNO,B.MGR
            FROM (SELECT EMPNO,MGR FROM EMP WHERE EMPNO=7839) A,EMP B
            WHERE A.EMPNO=B.MGR) A,EMP B
      WHERE A.EMPNO=B.MGR) A,EMP B
WHERE A.EMPNO=B.MGR;