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

这篇文章主要为大家展示了“ORACLE分层查询start with和connect by怎么用”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“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值，因此填充的空格数将根据各自的层号确定，空格再与层号拼接，结果显示出这种层次关系。

只查看第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;

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'

/

在这个查询中，仅剪去了树中单个节点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';

/

                           












这个查询结果就除了剪去单个节点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';

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

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