这是oracle中层次(递归)查询的一种用法。 离上一次正儿八经使用Oracle已经有一段日子了,不免荒疏。1. 层次查询(connect by查询) 它的语法是:SELECT * FROM WHERE START WITH CONNECT BY ORDER BY col1, col2 ... 其中,start with 表示的是根记录行,connect by 指定了父记录行和子记录行之间的关系,在层次查询中,条件表达式必须使用prior操作符来指定父记录行,如:... PRIOR expr = expr or ... expr = PRIOR expr 如果connect by 条件是一个组合条件,那么只有一个条件需要prior操作符,如:CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id 不过,connect by 不能包含子查询。 prior是一个二元操作符,最常见的是用于列值相等的比较,它让Oracle使用对应列的父亲行的值。使用非相等比较,极有可能倒致查询陷入无穷循环,以出错终止。与join的组合, 如果where子句有join操作,在执行connect by之前会先执行join 如果where子句没有join操作,会在执行connect by之后再执行这些条件 Oracle会执行下述步骤来完成connect by操作: 1. 执行条件过滤,取决于是否有join操作 2. 选择层次中满足start with条件的的根行,即层次树中第一层的节点行 3. 选择每个根行的子行,每个子行必须满足connect by条件中对应于根行的条件 4. 选择子行的后代,每次都使用子行与对应的父行进行connect by 匹配 5. 如果where中不含有join操作,那么Oracle将在执行完connect by操作之后移掉不符合条件的那么记录行。 当一行既是父行,也是子行时,就会产生死循环。例,找出雇员及经理之间的关系的结果: SELECT employee_id, last_name, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID LAST_NAME MANAGER_ID ----------- ------------------------- ---------- 101 Kochhar 100 108 Greenberg 101 109 Faviet 108 110 Chen 108 111 Sciarra 108 112 Urman 108 113 Popp 108 200 Whalen 101
举子例子说明, 对emp表,如果以条件“where mgr is NULL”开始,先会产生没有经理的那些行,以这些行作为根,开始搜索,再执行“CONNECT BY PRIOR EMPNO = MGR”,下面是这个查询的具体过程: Using EMP, the start with SET is:[email protected]> select ename, empno, mgr from emp 2 where mgr is null;ENAME EMPNO MGR ---------- ---------- ---------- KING 7839Now, if we do the "connect by manually" we would find:[email protected]> select ename, empno, mgr 2 from emp where mgr = 7839;ENAME EMPNO MGR ---------- ---------- ---------- JONES 7566 7839 BLAKE 7698 7839 CLARK 7782 [email protected]> KINGS empno is the prior empno. If we build the entire hierarch -- we have:[email protected]> select lpad(' ',level*2,' ')||ename ename, empno, mgr 2 from emp 3 START WITH MGR IS NULL 4 CONNECT BY PRIOR EMPNO = MGR 5 /ENAME EMPNO MGR --------------- ---------- ---------- KING 7839 JONES 7566 7839 SCOTT 7788 7566 ADAMS 7876 7788 FORD 7902 7566 SMITH 7369 7902 BLAKE 7698 7839 ALLEN 7499 7698 WARD 7521 7698 MARTIN 7654 7698 TURNER 7844 7698 JAMES 7900 7698 CLARK 7782 7839 MILLER 7934 778214 rows selected.
上面的例子从根往下查的语句就是 SELECT * FROM a WHERE START WITH gid=001 CONNECT BY PRIOR father_gid = gid 出来的结果就是 001 0 101 001 102 001 103 001 1001 101 1002 102 第二句从子往上查 SELECT * FROM a WHERE START WITH gid=1002 CONNECT BY gid =PRIOR father_gid 出来的结果就是 1002 102 102 001 101 0 大致应该是这样,语句可能有写的不对的地方,不过大致意思就是这样的
离上一次正儿八经使用Oracle已经有一段日子了,不免荒疏。1. 层次查询(connect by查询)
它的语法是:SELECT *
FROM
WHERE
START WITH
CONNECT BY
ORDER BY col1, col2 ...
其中,start with 表示的是根记录行,connect by 指定了父记录行和子记录行之间的关系,在层次查询中,条件表达式必须使用prior操作符来指定父记录行,如:... PRIOR expr = expr
or
... expr = PRIOR expr
如果connect by 条件是一个组合条件,那么只有一个条件需要prior操作符,如:CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id
不过,connect by 不能包含子查询。
prior是一个二元操作符,最常见的是用于列值相等的比较,它让Oracle使用对应列的父亲行的值。使用非相等比较,极有可能倒致查询陷入无穷循环,以出错终止。与join的组合, 如果where子句有join操作,在执行connect by之前会先执行join
如果where子句没有join操作,会在执行connect by之后再执行这些条件
Oracle会执行下述步骤来完成connect by操作:
1. 执行条件过滤,取决于是否有join操作
2. 选择层次中满足start with条件的的根行,即层次树中第一层的节点行
3. 选择每个根行的子行,每个子行必须满足connect by条件中对应于根行的条件
4. 选择子行的后代,每次都使用子行与对应的父行进行connect by 匹配
5. 如果where中不含有join操作,那么Oracle将在执行完connect by操作之后移掉不符合条件的那么记录行。
当一行既是父行,也是子行时,就会产生死循环。例,找出雇员及经理之间的关系的结果:
SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID LAST_NAME MANAGER_ID
----------- ------------------------- ----------
101 Kochhar 100
108 Greenberg 101
109 Faviet 108
110 Chen 108
111 Sciarra 108
112 Urman 108
113 Popp 108
200 Whalen 101
举子例子说明,
对emp表,如果以条件“where mgr is NULL”开始,先会产生没有经理的那些行,以这些行作为根,开始搜索,再执行“CONNECT BY PRIOR EMPNO = MGR”,下面是这个查询的具体过程:
Using EMP, the start with SET is:[email protected]> select ename, empno, mgr from emp
2 where mgr is null;ENAME EMPNO MGR
---------- ---------- ----------
KING 7839Now, if we do the "connect by manually" we would find:[email protected]> select ename, empno, mgr
2 from emp where mgr = 7839;ENAME EMPNO MGR
---------- ---------- ----------
JONES 7566 7839
BLAKE 7698 7839
CLARK 7782 [email protected]> KINGS empno is the prior empno. If we build the entire hierarch -- we have:[email protected]> select lpad(' ',level*2,' ')||ename ename, empno, mgr
2 from emp
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR
5 /ENAME EMPNO MGR
--------------- ---------- ----------
KING 7839
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 778214 rows selected.
gid father_gid
001 0
002 0
003 0
101 001
102 001
103 001
201 002
202 002
203 002
1001 101
1002 102
一个典型的树型结构
可能会有两种要求,一种是查GID=001及其下属子纪录
001 0
101 001
102 001
103 001
1001 101
1002 102
另一种查gid=1002上属所有父纪录
1002 102
102 001
101 0
最常见的比如说一个单位的组织结构(一个领导多个下属,一个下属只有一个直属上司,多层结构)
SELECT *
FROM a
WHERE
START WITH gid=001
CONNECT BY PRIOR father_gid = gid
出来的结果就是
001 0
101 001
102 001
103 001
1001 101
1002 102
第二句从子往上查
SELECT *
FROM a
WHERE
START WITH gid=1002
CONNECT BY gid =PRIOR father_gid
出来的结果就是
1002 102
102 001
101 0 大致应该是这样,语句可能有写的不对的地方,不过大致意思就是这样的
CONNECT BY PRIOR father_gid = gid 应该说用先前的gid去匹配father_gid,也就是所谓的向下
CONNECT BY gid =PRIOR father_gid 用第一条的father_gid去匹配gid,也就是向上
就是说向下向上应该跟具体字段有关的,关键字只是起到一个标识的作用!仅供参考下