对于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;
与图1中的level=4的值一致。
通过第四步查询子表中新添加的行,再次执行递归子查询。这次,该查询不生成结果。
以上为个人理解学习分解,如有较好的方式描述,可互相学习。
示例演示
以scott用户下的emp表举例查询对应效果,emp表有个字段,一个是empno(员工编号),另一个是mgr(上级经理编号)
SELECT * FROM EMP;
1.向下递归遍历
SELECT * FROM EMP CONNECT BY MGR= PRIOR EMPNO START WITH EMPNO=7839;
2.向上递归遍历
SELECT * FROM EMP CONNECT BY PRIOR MGR=EMPNO START WITH EMPNO=7844;