层次查询用于检索具有层次结构的表行数据。例如,雇员表(emp)存放着层次数据,其中雇员king是顶级雇员(president:总裁),雇员jones、blake、clark是king的下一级雇员(manager :经理),雇员scott和ford直属于jones雇员管理。当表具有层次结构数据时,通过层次查询可以直观显示数据结果,并显示其数据间的层次关系。语法如下:
select [level],column,expr...from table
[where condition]
start with condition
connect by [prior column1=column2 | column1=prior column2];
在emp表中empno是父键列,mgr是子键列。查询可以用从顶向下和自底向上两种方式。
1、使用prior column1=column2从顶向下查询:
SQL> select lpad(' ',3*(level-1))||ename ename, LPAD(' ',3*(level-1))||job job from emp
2 start with ename='JONES'
3 connect by prior empno=mgr;
ENAME JOB
-------------------- ------------------------------
JONES MANAGER
SCOTT ANALYST
ADAMS CLERK
FORD ANALYST
SMITH CLERK
2、使用column1=prior column2 从顶向下显示数据
SQL> select lpad(' ',3*(level-1))||ename ename,
2 lpad(' ',3*(level-1))||job job from emp
3 start with ename='BLAKE'
4 connect by mgr=prior empno;
ENAME JOB
-------------------- ------------------------------
BLAKE MANAGER
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
JAMES CLERK
3、使用prior column1=column2从底向上显示数据
SQL> select lpad(' ',3*(level-1))||ename ename,
2 lpad(' ',3*(level-1))||job job from emp
3 start with ename='SMITH' connect by prior mgr=empno;
ENAME JOB
-------------------- ------------------------------
SMITH CLERK
FORD ANALYST
JONES MANAGER
KING PRESIDENT
4、使用column1=prior column2自底向上显示数据
SQL> select lpad(' ',3*(level-1))||ename ename,
2 lpad(' ',3*(level-1))||job job from emp
3 start with ename='ALLEN' connect by empno=prior mgr;
ENAME JOB
-------------------- ------------------------------
ALLEN SALESMAN
BLAKE MANAGER
KING PRESIDENT
总结:如若想自顶向下显示 则prior后面跟的应该是父键列,如若是自底向上显示则prior后面跟的应该是子键列。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23655288/viewspace-733239/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23655288/viewspace-733239/