SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [CONNECT BY PRIOR condition(s)]--------------------------------------------------- 层次查询是通过start with和connect by 子句标识的。 1.其中level关键字是可选的,表示等级,表示root,2 表示root 的child,其他相同的规则。 2.From之后可以是table,view但是只能是一个table。 3.Where条件限制了查询返回的行,但是不影响层次关系,不满足条件的节点不返回,但是 这个不满足条件的节点的下层child不受影响。 4.Start with是表示开始节点,对于一个真实的层次关系,必须要有这个子句,但是不是必须 的,后面详细介绍。 5.connect by prior 是指定父子关系,其中prior 的位置不一定要在connect by之后, 对于一个真实的层次关系,这也是必须的。 对于from是视图的,那么这个view不能包含join。 5.2.2 遍历树 Start with子句: 首先必须确定startpoint,通过start with子句,后面加条件,这个条件是任何合法的条 件表达式。 Start with确定将哪行作为root,如果没有start with,则每行都当作root,然后查找其 后代,这不是一个真实的查询。Start with后面可以使用子查询,如果有where条件,则会 截断层次中的相关满足条件的节点,但是不影响整个层次结构。可以带多个条件。 对于s_emp,从root title=president 开始,语句如下: select level,id,manager_id,last_name,title from s_emp start with manager_id is null connect by prior id=manager_id; 这个语句与下面的语句等价,将start with 后面的条件换成子查询。 select level,id,manager_id,last_name,title from s_emp start with title=(select title from s_emp where manager_id is null) connect by prior id=manager_id; 注意:start with 和connect by prior不是ANSI标准sql。 Connect by子句: Connect by与prior 确定一个层次查询的条件和遍历的方向(prior 确定)。 Connect by prior column_1=column_2;(其中prior表示前一个节点的意思,可以在connect by 等号的前后,列之前,也可以放到select中的列之前)。 Connect by也可以带多个条件,比如 connect by prior id=manager_id and id>10;
例如: select level,id,manager_id,last_name, title from s_emp start with manager_id=2 connect by id=prior manager_id;--自下而上遍历 2. 自底向上遍历:就是先由最底层的子节点,遍历一直找到根节点。与上面的相反。 Connect by 之后不能有子查询,但是可以加其他条件,比如加上and id !=2等。这句话则会 截断树枝,如果id=2 的这个节点下面有很多子孙后代,则全部截断不显示。比如下面的句 子: select level,id,manager_id,last_name,title from s_emp start with title=(select title from s_emp where manager_id is null) connect by prior id=manager_id and id!=2; 不来不加上id!=2,共有25 条记录,现在加上这个条件只有9 条记录了,因为id=2 的 后代包括自己共有16条记录,全部被截断
这是oracle中emp的树形图 --你所谓的用where来过滤一些条件,无非就是将树中的某些节点及其叶子节点也删除。 --首先我们来查看一下这棵树 SELECT empno,mgr,ename FROM emp START WITH empno=7839 CONNECT BY PRIOR empno=mgr; /* EMPNO MGR ENAME ----- ----- ---------- 7839 KING 7566 7839 JONES 7788 7566 SCOTT 7876 7788 ADAMS 7902 7566 FORD 7369 7902 SMITH 7698 7839 BLAKE 7499 7698 ALLEN 7521 7698 WARD 7654 7698 MARTIN 7844 7698 TURNER 7900 7698 JAMES 7782 7839 CLARK 7934 7782 MILLER */ --下面我们来从根节点往下来遍历这棵树,LEVEL列显示此节点在数的第几层 SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename FROM emp START WITH empno=7839 CONNECT BY PRIOR empno=mgr; /* LEVEL LPAD('',2*(LEVEL-1))||ENAME ---------- -------------------------------------------------------------------------------- 1 KING 2 JONES 3 SCOTT 4 ADAMS 3 FORD 4 SMITH 2 BLAKE 3 ALLEN 3 WARD 3 MARTIN 3 TURNER 3 JAMES 2 CLARK 3 MILLER */ --我们在从某个节点(ADAMS)反向跟节点来遍历这棵树 SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename FROM emp START WITH ename='ADAMS' CONNECT BY PRIOR mgr=empno ORDER BY LEVEL DESC; /* LEVEL LPAD('',2*(LEVEL-1))||ENAME ---------- -------------------------------------------------------------------------------- 4 KING 3 JONES 2 SCOTT 1 ADAMS
*/ --计算树的层数 select count(distinct level) from emp start with empno=7839 connect by prior empno=mgr; /* COUNT(DISTINCTLEVEL) -------------------- 4 */ --我们将blake这个节点删除 -- SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename FROM emp WHERE ename!='BLAKE' START WITH empno=7839 CONNECT BY PRIOR empno=mgr; /* LEVEL LPAD('',2*(LEVEL-1))||ENAME ---------- -------------------------------------------------------------------------------- 1 KING 2 JONES 3 SCOTT 4 ADAMS 3 FORD 4 SMITH 3 ALLEN 3 WARD 3 MARTIN 3 TURNER 3 JAMES 2 CLARK 3 MILLER */ --我们看到blake这个节点下的叶子节点还存在的,我们将其也删除掉 -- SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename FROM emp START WITH empno=7839 CONNECT BY PRIOR empno=mgr AND ename!='BLAKE'; /* LEVEL LPAD('',2*(LEVEL-1))||ENAME ---------- -------------------------------------------------------------------------------- 1 KING 2 JONES 3 SCOTT 4 ADAMS 3 FORD 4 SMITH 2 CLARK 3 MILLER */ --这个应该就是你想要的结果吧! --
比如:
select ... from
(select ... from .....where ...)
start with...
connect by ....
select ...
from (select ... from start with ... connect by ...)
where ...
你用select * from where start with connect by prior 这里的where是在递归之后,或者同时
你想先过滤的话 可以嵌套一层,在里边过滤,再递归
FROM table
[WHERE condition(s)]
[CONNECT BY PRIOR condition(s)]---------------------------------------------------
层次查询是通过start with和connect by 子句标识的。
1.其中level关键字是可选的,表示等级,表示root,2 表示root 的child,其他相同的规则。
2.From之后可以是table,view但是只能是一个table。
3.Where条件限制了查询返回的行,但是不影响层次关系,不满足条件的节点不返回,但是
这个不满足条件的节点的下层child不受影响。
4.Start with是表示开始节点,对于一个真实的层次关系,必须要有这个子句,但是不是必须
的,后面详细介绍。
5.connect by prior 是指定父子关系,其中prior 的位置不一定要在connect by之后,
对于一个真实的层次关系,这也是必须的。
对于from是视图的,那么这个view不能包含join。
5.2.2 遍历树
Start with子句:
首先必须确定startpoint,通过start with子句,后面加条件,这个条件是任何合法的条
件表达式。
Start with确定将哪行作为root,如果没有start with,则每行都当作root,然后查找其
后代,这不是一个真实的查询。Start with后面可以使用子查询,如果有where条件,则会
截断层次中的相关满足条件的节点,但是不影响整个层次结构。可以带多个条件。
对于s_emp,从root title=president 开始,语句如下:
select level,id,manager_id,last_name,title from s_emp
start with manager_id is null
connect by prior id=manager_id;
这个语句与下面的语句等价,将start with 后面的条件换成子查询。
select level,id,manager_id,last_name,title from s_emp
start with title=(select title from s_emp where manager_id is null)
connect by prior id=manager_id;
注意:start with 和connect by prior不是ANSI标准sql。
Connect by子句:
Connect by与prior 确定一个层次查询的条件和遍历的方向(prior 确定)。
Connect by prior column_1=column_2;(其中prior表示前一个节点的意思,可以在connect
by 等号的前后,列之前,也可以放到select中的列之前)。
Connect by也可以带多个条件,比如 connect by prior id=manager_id and id>10;
select level,id,manager_id,last_name, title from s_emp
start with manager_id=2
connect by id=prior manager_id;--自下而上遍历
2. 自底向上遍历:就是先由最底层的子节点,遍历一直找到根节点。与上面的相反。
Connect by 之后不能有子查询,但是可以加其他条件,比如加上and id !=2等。这句话则会
截断树枝,如果id=2 的这个节点下面有很多子孙后代,则全部截断不显示。比如下面的句
子:
select level,id,manager_id,last_name,title from s_emp
start with title=(select title from s_emp where manager_id is null)
connect by prior id=manager_id and id!=2;
不来不加上id!=2,共有25 条记录,现在加上这个条件只有9 条记录了,因为id=2 的
后代包括自己共有16条记录,全部被截断
--你所谓的用where来过滤一些条件,无非就是将树中的某些节点及其叶子节点也删除。
--首先我们来查看一下这棵树
SELECT empno,mgr,ename
FROM emp
START WITH empno=7839
CONNECT BY PRIOR empno=mgr;
/*
EMPNO MGR ENAME
----- ----- ----------
7839 KING
7566 7839 JONES
7788 7566 SCOTT
7876 7788 ADAMS
7902 7566 FORD
7369 7902 SMITH
7698 7839 BLAKE
7499 7698 ALLEN
7521 7698 WARD
7654 7698 MARTIN
7844 7698 TURNER
7900 7698 JAMES
7782 7839 CLARK
7934 7782 MILLER
*/
--下面我们来从根节点往下来遍历这棵树,LEVEL列显示此节点在数的第几层
SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename
FROM emp
START WITH empno=7839
CONNECT BY PRIOR empno=mgr;
/*
LEVEL LPAD('',2*(LEVEL-1))||ENAME
---------- --------------------------------------------------------------------------------
1 KING
2 JONES
3 SCOTT
4 ADAMS
3 FORD
4 SMITH
2 BLAKE
3 ALLEN
3 WARD
3 MARTIN
3 TURNER
3 JAMES
2 CLARK
3 MILLER
*/
--我们在从某个节点(ADAMS)反向跟节点来遍历这棵树
SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename
FROM emp
START WITH ename='ADAMS'
CONNECT BY PRIOR mgr=empno
ORDER BY LEVEL DESC;
/*
LEVEL LPAD('',2*(LEVEL-1))||ENAME
---------- --------------------------------------------------------------------------------
4 KING
3 JONES
2 SCOTT
1 ADAMS
*/
--计算树的层数
select count(distinct level)
from emp
start with empno=7839
connect by prior empno=mgr;
/*
COUNT(DISTINCTLEVEL)
--------------------
4
*/
--我们将blake这个节点删除
--
SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename
FROM emp
WHERE ename!='BLAKE'
START WITH empno=7839
CONNECT BY PRIOR empno=mgr;
/*
LEVEL LPAD('',2*(LEVEL-1))||ENAME
---------- --------------------------------------------------------------------------------
1 KING
2 JONES
3 SCOTT
4 ADAMS
3 FORD
4 SMITH
3 ALLEN
3 WARD
3 MARTIN
3 TURNER
3 JAMES
2 CLARK
3 MILLER
*/
--我们看到blake这个节点下的叶子节点还存在的,我们将其也删除掉
--
SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename
FROM emp
START WITH empno=7839
CONNECT BY PRIOR empno=mgr
AND ename!='BLAKE';
/*
LEVEL LPAD('',2*(LEVEL-1))||ENAME
---------- --------------------------------------------------------------------------------
1 KING
2 JONES
3 SCOTT
4 ADAMS
3 FORD
4 SMITH
2 CLARK
3 MILLER
*/
--这个应该就是你想要的结果吧!
--
http://student.csdn.net/space.php?uid=442626&do=album&picid=43092