ITPub博客

首页 > Linux操作系统 > Linux操作系统 > start with...connect by 层次查询

start with...connect by 层次查询

原创 Linux操作系统 作者:haha牛 时间:2012-06-19 11:24:00 0 删除 编辑

    层次查询用于检索具有层次结构的表行数据。例如,雇员表(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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-04-04

  • 博文量
    102
  • 访问量
    299476