ORACLE分层查询startwith和connectby怎么用
这篇文章主要为大家展示了“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怎么用”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联行业资讯频道!
分享题目:ORACLE分层查询startwith和connectby怎么用
本文网址:http://myzitong.com/article/pdpiei.html