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

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

 层次化查询,即树型结构查询,是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).判断该节点是否存在有子节点,如果有,则访问最左侧未被访问的子节点,转到1否则下一步

           如上例中prior_condition为empno = mgr(下一条查询记录返回的mgr值等于前一条记录的empno),即子节点的mgr等于父节点的empno,在此时下一条的mgr为7839的记录

       4).当节点为叶节点,则访问完毕,进入下一步,否则,转到3

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

      

    --伪列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(' ',* 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(' ',* 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(' ',* 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(' ',* 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(' ',* 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(' ',* 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(' ',* 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(' ',* 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.


相关文章
|
28天前
|
SQL 监控 关系型数据库
一键开启百倍加速!RDS DuckDB 黑科技让SQL查询速度最高提升200倍
RDS MySQL DuckDB分析实例结合事务处理与实时分析能力,显著提升SQL查询性能,最高可达200倍,兼容MySQL语法,无需额外学习成本。
|
28天前
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
27天前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
1月前
|
SQL XML Java
通过MyBatis的XML配置实现灵活的动态SQL查询
总结而言,通过MyBatis的XML配置实现灵活的动态SQL查询,可以让开发者以声明式的方式构建SQL语句,既保证了SQL操作的灵活性,又简化了代码的复杂度。这种方式可以显著提高数据库操作的效率和代码的可维护性。
153 18
|
1月前
|
SQL 人工智能 数据库
【三桥君】如何正确使用SQL查询语句:避免常见错误?
三桥君解析了SQL查询中的常见错误和正确用法。AI产品专家三桥君通过三个典型案例:1)属性重复比较错误,应使用IN而非AND;2)WHERE子句中非法使用聚合函数的错误,应改用HAVING;3)正确的分组查询示例。三桥君还介绍了学生、课程和选课三个关系模式,并分析了SQL查询中的属性比较、聚合函数使用和分组查询等关键概念。最后通过实战练习帮助读者巩固知识,强调掌握这些技巧对提升数据库查询效率的重要性。
92 0
|
2月前
|
SQL
SQL中如何删除指定查询出来的数据
SQL中如何删除指定查询出来的数据
|
3月前
|
SQL 存储 弹性计算
OSS Select 加速查询:10GB CSV 文件秒级过滤的 SQL 语法优化技巧
OSS Select 可直接在对象存储上执行 SQL 过滤,跳过文件下载,仅返回所需数据,性能比传统 ECS 方案提升 10~100 倍。通过减少返回列、使用等值查询、避免复杂函数、分区剪枝及压缩优化等技巧,可大幅降低扫描与传输量,显著提升查询效率并降低成本。
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
434 13
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
275 9

热门文章

最新文章