SQL基础-- 层次化查询 START BY CONNECT BY PRIOR

               

--======================================================

--SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

--======================================================

 

    层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成,其语法如下:

       SELECT [LEVEL] ,column,expression,...

       FROM table_name

       [WHERE where_clause]

       [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

      

       LEVEL:为伪列,用于表示树的层次

       start_condition：层次化查询的起始条件

       prior_condition：定义父节点和子节点之间的关系

   

    --使用start with ...connect by prior 从根节点开始遍历

    SQL> select empno,mgr,ename,job from emp

      2  start with empno = 7839

      3  connect by prior empno = mgr;

 

        EMPNO        MGR ENAME      JOB

    ---------- ---------- ---------- ---------

         7839            KING       PRESIDENT

         7566       7839 JONES      MANAGER

         7788       7566 SCOTT      ANALYST

         7876       7788 ADAMS      CLERK

         7902       7566 FORD       ANALYST

         7369       7902 SMITH      CLERK

         7698       7839 BLAKE      MANAGER

         7499       7698 ALLEN      SALESMAN