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

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

为什么80%的码农都做不了架构师？>>>   hot3.png

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

--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

         7521       7698 WARD       SALESMAN

         7654       7698 MARTIN     SALESMAN

         7844       7698 TURNER     SALESMAN

 

        EMPNO        MGR ENAME      JOB

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

         7900       7698 JAMES      CLERK

         7782       7839 CLARK      MANAGER

         7934       7782 MILLER     CLERK

 

    14 rows selected.

   

    树型结构遍历过程(通过上面的查询来描述)

       1).从根节点开始(即where_clause中的条件,如果为非根节点则分根节点作为根节点开始遍历,如上例empno = 7839)

       2).遍历根节点(得到empno = 7839记录的相关信息)

       3).判断该节点是否存在由子节点，如果则访问最左侧未被访问的子节点,转到),否则下一步

           如上例中prior_condition为empno = mgr,即子节点的mgr等于父节点的empno,在此时mgr为7839的记录

       4).当节点为叶节点，则访问完毕,否则,转到)

       5).返回到该节点的父节点,转到)

      

    --伪列level的使用

    --注意connect by prior empno = mgr 的理解

    --prior表示前一条记录,即下一条返回记录的mgr应当等于前一条记录的empno

 

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

      2  start with ename = 'KING'

      3  connect by prior empno = mgr

      4  order by level;

 

        LEVEL      EMPNO        MGR ENAME      JOB

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

            1       7839            KING       PRESIDENT

            2       7566       7839 JONES      MANAGER

            2       7698       7839 BLAKE      MANAGER

            2       7782       7839 CLARK      MANAGER

            3       7902       7566 FORD       ANALYST

            3       7521       7698 WARD       SALESMAN

            3       7900       7698 JAMES      CLERK

            3       7934       7782 MILLER     CLERK

            3       7499       7698 ALLEN      SALESMAN

            3       7788       7566 SCOTT      ANALYST

            3       7654       7698 MARTIN     SALESMAN

 

        LEVEL      EMPNO        MGR ENAME      JOB

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

            3       7844       7698 TURNER     SALESMAN

            4       7876       7788 ADAMS      CLERK

            4       7369       7902 SMITH      CLERK

   

    --获得层次数

    SQL> select count(distinct level) "Level" from emp

      2  start with ename = 'KING'

      3  connect by prior empno = mgr;

 

        Level

    ----------

            4 

            

    --格式化层次查询结果(使用左填充* level - 1个空格)

    SQL> col Ename for a30

    SQL> select level,

      2    lpad(' ',2 * level - 1) || ename as "Ename",

      3    job

      4  from emp

      5  start with ename = 'KING'

      6  connect by prior empno = mgr;

 

        LEVEL Ename                          JOB

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

            1  KING                          PRESIDENT

            2    JONES                       MANAGER

            3      SCOTT                     ANALYST

            4        ADAMS                   CLERK

            3      FORD                      ANALYST

            4        SMITH                   CLERK

            2    BLAKE                       MANAGER

            3      ALLEN                     SALESMAN

            3      WARD                      SALESMAN

            3      MARTIN                    SALESMAN

            3      TURNER                    SALESMAN

 

        LEVEL Ename                          JOB

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

            3      JAMES                     CLERK

            2    CLARK                       MANAGER

            3      MILLER                    CLERK

 

    14 rows selected.

   

    --从非根节点开始遍历(只需修改start with 中的条件即可)

    SQL> select level,

      2    lpad(' ',2 * level - 1) || ename as "Ename",

      3    job

      4  from emp

      5  start with ename = 'SCOTT'

      6  connect by prior empno = mgr;

 

        LEVEL Ename                          JOB

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

            1  SCOTT                         ANALYST

            2    ADAMS                       CLERK

 

    --从下向上遍历(交换connect by prior中的条件即可,使用mgr = empno)

    --注意connect by prior mgr = empno 的理解

    --prior表示前一条记录,即下一条返回记录的empno应当等于前一条记录的mgr

 

    SQL> select level,

      2    lpad(' ',2 * level - 1) || ename as "Ename",

      3    job

      4  from emp

      5  start with ename = 'SCOTT'

      6  connect by prior mgr = empno;

 

        LEVEL Ename                          JOB

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

            1  SCOTT                         ANALYST

            2    JONES                       MANAGER

            3      KING                      PRESIDENT

            

    --从下向上遍历(也可以将prior置于等号右边,得到相同的结果)

    SQL> select level,

      2    lpad(' ',2 * level - 1) || ename as "Ename",

      3    job

      4  from emp

      5  start with ename = 'SCOTT'

      6  connect by empno = prior mgr;

 

        LEVEL Ename                          JOB

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

            1  SCOTT                         ANALYST

            2    JONES                       MANAGER

            3      KING                      PRESIDENT

            

    --从层次查询中删除节点和分支

    SQL> select level,

      2    lpad(' ',2 * level - 1) || ename as "Ename"

      3    ,job

      4  from emp

      5  where ename != 'SCOTT'    --通过where子句来过滤SCOTT用户，但SCOTT的下属ADAMS并没有过滤掉

      6  start with empno = 7839   

      7  connect by prior empno = mgr;

 

        LEVEL Ename                JOB

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

            1  KING                PRESIDENT

            2    JONES             MANAGER

            4        ADAMS         CLERK

            3      FORD            ANALYST

            4        SMITH         CLERK

            2    BLAKE             MANAGER

            3      ALLEN           SALESMAN

            3      WARD            SALESMAN

            3      MARTIN          SALESMAN

            3      TURNER          SALESMAN

            3      JAMES           CLERK

 

        LEVEL Ename                JOB

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

            2    CLARK             MANAGER

            3      MILLER          CLERK

 

    13 rows selected.

    

    --通过将过滤条件由where 子句的内容移动到connect by prior 子句中过滤掉SCOTT及其下属

    SQL> select level,

      2    lpad(' ',2 * level - 1) || ename as "Ename"

      3    ,job

      4  from emp

      5  start with empno = 7839

      6  connect by prior empno = mgr and ename != 'SCOTT';

 

        LEVEL Ename                JOB

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

            1  KING                PRESIDENT

            2    JONES             MANAGER

            3      FORD            ANALYST

            4        SMITH         CLERK

            2    BLAKE             MANAGER

            3      ALLEN           SALESMAN

            3      WARD            SALESMAN

            3      MARTIN          SALESMAN

            3      TURNER          SALESMAN

            3      JAMES           CLERK

            2    CLARK             MANAGER

 

        LEVEL Ename                JOB

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

            3      MILLER          CLERK

 

    12 rows selected.

   

    --在层次化查询中增加过滤条件或使用子查询

    SQL> select level,

      2    lpad(' ',2 * level - 1) || ename as "Ename"

      3    ,job

      4  from emp

      5  where sal > 2500

      6  start with empno = 7839

      7  connect by prior empno = mgr                     

      8  ;

 

        LEVEL Ename                JOB

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

            1  KING                PRESIDENT

            2    JONES             MANAGER

            3      SCOTT           ANALYST

            3      FORD            ANALYST

            2    BLAKE             MANAGER

            

    SQL> select level,

      2    lpad(' ',2 * level - 1) || ename as "Ename"

      3    ,job

      4  from emp

      5  where sal > (select avg(sal) from emp)

      6  start with empno = 7839

      7  connect by prior empno = mgr ;

 

        LEVEL Ename                JOB

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

            1  KING                PRESIDENT

            2    JONES             MANAGER

            3      SCOTT           ANALYST

            3      FORD            ANALYST

            2    BLAKE             MANAGER

            2    CLARK             MANAGER

 

    6 rows selected.

   

    更多参考：

 

Oracle 数据库实例启动关闭过程

 

Oracle 10g SGA 的自动化管理

 

使用OEM,SQL*Plus,iSQL*Plus 管理Oracle实例

 

Oracle实例和Oracle数据库(Oracle体系结构)

 

SQL 基础-->常用函数

 

SQL基础-->过滤和排序

 

SQL 基础-->SELECT 查询

 

 

 



原文链接： http://blog.csdn.net/robinson_0612/article/details/5616877

转载于:https://my.oschina.net/dtec/blog/47452

更多相关：

  • 边缘计算架构_Openshift为边缘计算提供三节点架构部署

    当一个IT组织开始走到需要实施网络边缘的旅程时，他们很快意识到面对的挑战与他们在传统数据中心内所经历的挑战不同。 第一个挑战是空间。与更大的核心或区域数据中心同类产品相比，许多边缘站点的物理尺寸更小，因此，需要仔细计划好，尝试在未为其专门设计的空间中安装硬件。  第二个挑战是运行环境。还必须解决的可能面对的冷热温度变化 ，天气，无...

  • node 获取表单数据 为空_数据结构与算法(python)单向循环链表

    单向循环链表单链表的一个变形是单向循环链表, 链表的最后一个节点的next域不再为None, 而是指向链表的头节点.单向循环链表如图所示:单向循环链表同样单向循环链表也是要使用python来对它的基本功能进行一个封装. 总体大致的功能如下:is_empty() 判断链表是否为空length() 返回链表的长度travel() 遍历ad...

  • 剑指offer：面试题36. 二叉搜索树与双向链表

    题目： 二叉搜索树与双向链表 输入一棵二叉搜索树，将该二叉搜索树转换成一个排序的循环双向链表。要求不能创建任何新的节点，只能调整树中节点指针的指向。 为了让您更好地理解问题，以下面的二叉搜索树为例： 我们希望将这个二叉搜索树转化为双向循环链表。链表中的每个节点都有一个前驱和后继指针。对于双向循环链表，第一个节点的前驱是最后一...

  • 剑指offer：面试题18. 删除链表的节点

    题目：删除链表的节点 给定单向链表的头指针和一个要删除的节点的值，定义一个函数删除该节点。 返回删除后的链表的头节点。 注意：此题对比原题有改动 示例 1: 输入: head = [4,5,1,9], val = 5 输出: [4,1,9] 解释: 给定你链表中值为 5 的第二个节点，那么在调用了你的函数之后，该链表应变为...

  • 从零开始的ROS四轴机械臂控制-目录

    【从零开始的ROS四轴机械臂控制】（一）- 实际模型制作、Solidworks文件转urdf与rviz仿真 一、模型制作 1.实际模型制作 2.Solidworks模型制作 二、Solidworks文件转urdf 1.sw_urdf_exporter插件 2.添加坐标系和转轴 3.导出urdf文件 三、rivz仿真...

©2024 11GX.COM