ORACLE分层查询start with和connect by怎么用

70次阅读
没有评论

共计 3142 个字符,预计需要花费 8 分钟才能阅读完成。

这篇文章主要为大家展示了“ORACLE 分层查询 start with 和 connect by 怎么用”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让丸趣 TV 小编带领大家一起研究并学习一下“ORACLE 分层查询 start with 和 connect by 怎么用”这篇文章吧。

分层查询是 select 语句的扩展,目的是迅速找出表中列 - 列的隶属关系。

19.1 树的遍历

ORACLE 是一个关系数据库管理系统, 它用表的形式组织数据, 在某些表中的数据还呈现出树型结构的联系。例如,我们现在讨论雇员信息表 EMP,其中含有雇员编号(EMPNO)和经理(MGR)两列,通过这两列反映出来的就是雇员之间领导和被领导的关系。他们之间的这种关系就是一种树结构。

树的遍历有两个方向

top–down 自上而下

即父亲找儿子,一个父亲可能有几个儿子,一个儿子可能有几个孙子,遍历不能丢了儿子,顺序以左为先。

down–top 自底向上

即儿子找父亲,一个儿子只能有一个父亲,所以顺序应该是:孙子 - 儿子 – 父亲 – 爷爷。

19.2 CONNECT BY 和 START WITH

在 SELECT 命令中使用 CONNECT BY 和 START WITH 子句可以查询表中的树型结构关系。其命令格式如下:

SELECT …

CONNECT BY {PRIOR 列名 1 = 列名 2 | 列名 1 =PRIOR 列名 2}

[START WITH];

19.3 关于 CONNECT BY 子句

理解 CONNECT BY PRIOR 子句至关重要,它确定了树的检索方向: 是 top — down(父 – 子)还是 down — top(子 – 父)。

在分层表中,表的父列与子列是确定的(身份固定),如:在 emp 表中 empno 是子列(下级),mgr 是父列(上级)。

RIOR 关键字就像一个箭头 (—),

connect by prior empno = mgr

connect by mgr = prior empno

两句语法等同,都是说 mgr(父)– empno(子),因此树的检索方向是 top — down。

connect by empno = prior mgr

connect by prior mgr = empno

两句语法等同,都是说 empno(子)– mgr( 父),因此树的检索方向是 down — top。

19.4 START WITH 子句为可选项,用来标识哪个节点作为查找树型结构的根节点。若该子句被省略,则表示所有满足查询条件的行作为根节点(每一行都会成为一个树根)。

例 1 以树结构方式显示 EMP 表的数据。

SQL select empno,ename,mgr from emp connect by prior empno=mgr start with empno=7839;

仔细看 empno 这一列输出的顺序,就是上图树状结构每一条分支(从根节点开始)的结构。

mgr(父)– empno(子),因此树的检索方向是 top — down。

例 2 从 SMITH 节点开始自底向上查找 EMP 的树结构。

SQL select empno,ename,mgr

from emp

connect by empno=prior mgr

start with empno=7369

/

在这种自底向上的查找过程中,只有树中的一枝被显示。

empno(子)– mgr( 父),因此树的检索方向是 down — top

19.5   定义查找起始节点

在自顶向下查询树结构时,不但可以从根节点开始,还可以定义任何节点为起始节点,以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。

例 3 查找 7566(JONES) 直接或间接领导的所有雇员信息。

SQL>SELECT EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH EMPNO=7566

/

START WITH 不但可以指定一个根节点,还可以指定多个根节点。

例 4 查找由 FORD 和 BLAKE 领导的所有雇员的信息。

SQL SELECT EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME IN (FORD , BLAKE)

/       父亲找儿子

19.6 使用 LEVEL

在查询中,可以使用伪列 LEVEL 显示每行数据的有关层次。LEVEL 将返回树型结构中当前节点的层次。

伪列 LEVEL 为数值型,可以在 SELECT 命令中用于各种计算。

例 5 使用 LEVEL 改变查询结果的显示形式。

SQL COLUMN LEVEL FORMAT A20

SQL SELECT LPAD(LEVEL,LEVEL*3,)

as LEVEL ,EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME= KING

/

在 SELECT 使用了函数 LPAD,该函数表示以 LEVEL* 3 个空格进行填充,由于不同行处于不同的节点位置,具有不同的 LEVEL 值,因此填充的空格数将根据各自的层号确定,空格再与层号拼接,结果显示出这种层次关系。

ORACLE 分层查询 start with 和 connect by 怎么用

只查看第 2 层的员工信息:

SQL select t1.* from (select level LNUM ,ename,mgr from emp connect by prior empno=mgr start with ename= KING) t1 where LNUM=2;

ORACLE 分层查询 start with 和 connect by 怎么用

19.7 节点和分支的裁剪

在对树结构进行查询时,可以去掉表中的某些行,也可以剪掉树中的一个分支,使用 WHERE 子句来限定树型结构中的单个节点,以去掉树中的单个节点,但它却不影响其后代节点(自顶向下检索时)或前辈节点(自底向顶检索时)。

SQL SELECT LPAD(LEVEL,LEVEL*3,)

as LEVEL ,EMPNO,ENAME,MGR

FROM EMP

WHERE ENAME SCOTT

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME= KING

ORACLE 分层查询 start with 和 connect by 怎么用/

ORACLE 分层查询 start with 和 connect by 怎么用ORACLE 分层查询 start with 和 connect by 怎么用

在这个查询中,仅剪去了树中单个节点 SCOTT。若希望剪去树结构中的某个分支,则要用 CONNECT BY 子句。CONNECT BY 子句是限定树型结构中的整个分支,既要剪除分支上的单个节点,也要剪除其后代节点(自顶向下检索时)或前辈节点(自底向顶检索时)。

例 8.显示 KING 领导下的全体雇员信息,除去 SCOTT 领导的一支。

SQL SELECT LPAD(LEVEL,LEVEL*3,) as LEVEL ,EMPNO,ENAME,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR AND ENAME!= SCOTT START WITH ENAME= KING

/

ORACLE 分层查询 start with 和 connect by 怎么用

ORACLE 分层查询 start with 和 connect by 怎么用ORACLE 分层查询 start with 和 connect by 怎么用

                           

这个查询结果就除了剪去单个节点 SCOTT 外,还将 SCOTT 的子节点 ADAMS 剪掉,即把 SCOTT 这个分支剪掉了。

当然 WHERE 子句可以和 CONNECT BY 子句联合使用,这样能够同时剪掉单个节点和树中的某个分支。

例 9.显示 KING 领导全体雇员信息,除去雇员 SCOTT,以及 BLAKE 领导的一支。

SCOTT@hyyk SELECT LPAD(LEVEL,LEVEL*3,) as LEVEL ,EMPNO,ENAME,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR AND ENAME!= SCOTT and ENAME!= BLAKE START WITH ENAME= KING

ORACLE 分层查询 start with 和 connect by 怎么用

ORACLE 分层查询 start with 和 connect by 怎么用

在使用 SELECT 语句来报告树结构报表时应当注意,CONNECT BY 子句不能作用于出现在 WHERE 子句中的表连接。如果需要进行连接,可以先用树结构建立一个视图,再将这个视图与其他表连接,以完成所需要的查询。

以上是“ORACLE 分层查询 start with 和 connect by 怎么用”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注丸趣 TV 行业资讯频道!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-20发表,共计3142字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)