ORACLE start with conect by 详解

小杨学JAVA

浏览: 885294 次
性别:
来自: 上海

最近访客更多访客>>

loginboot

cnlihao

raoqin

dawninautumn

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

oracle

<table>

<tbody>

<tr>

<td>

</div>

</td>

</tr>

<tr>

<td style="height: 200px;

" vAlign="top " width="670 " align="left ">

<span id="articlecontent " onmouseup="NewHighlight(event)" onmousedown="newhighlight = true;

<table>

<tbody>

<tr>

<td style="position: relative;

" width="670 ">

<h1 style="color: rgb(75, 75, 75);

font - family: Verdana,

Geneva,

Arial,

Helvetica,

sans - serif;

font - size: 14.7px;

margin - bottom: 10px;

" class="postTitle ">

<a style="color: rgb(26, 139, 200);

text - decoration: none;

" id="cb_post_title_url " class="postTitle2 " href="http: //www.cnblogs.com/caroline/archive/2011/12/25/2301083.html">

ORACLE--Connect By、Level、Start With的使用(Hierarchical query-层次查询)

</a>

</h1>

<div style="color: rgb(75, 75, 75); line-height: 19px; font-family: Verdana, Geneva, Arial, Helvetica, sans-serif; font-size: 13px; word-break: normal !important;"

id="cnblogs_post_body">

<table style="border-style: solid; border-color: rgb(192, 192, 192); width: 1026px;"

border="1" cellSpacing="1" cellPadding="2" bgColor="#d8d8c4">

<tbody>

<tr>

Syntax 1

</td>

CONNECT BY

</a>

[NOCYCLE] <condition>

START WITH

</a>

</td>

</tr>

<tr>

Syntax 2

</td>

START WITH

</a>

CONNECT BY

</a>

[NOCYCLE] <condition>

</td>

</tr>

</tbody>

</table>

参考网址:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:489772591421

</a>

http://psoug.org/reference/connectby.html

</a>

http://www.oradev.com/connect_by.jsp

</a>

http://philip.greenspun.com/sql/trees.html

</a>

查找员工编号为7369的领导:

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

SELECT

LEVEL

,E.

FROM

EMP E CONNECT

PRIOR E.MGR

E.EMPNO START

WITH

E.EMPNO

7876

ORDER

LEVEL

DESC

</pre>

</div>

start with

" -- this identifies all LEVEL=1 nodes in the tree

connect by

" -- describes how to walk from the parent nodes above to their children

and 

their childrens children.

Easiest to use an example on emp. If we start with "where mgr is NULL",

we generate the 

set of employees that have no mgr (they are the top of the tree). If we

CONNECT BY PRIOR EMPNO = /* current */ MGR

that will take all of the PRIOR records (the start with at first) and

find all records 

such that the MGR column equals their EMPNO (find all the records of people

managed by 

the people we started with).

使用WITH语句优化查询结果:

优化等级

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

WITH

(

SELECT

MAX

(

LEVEL

)

LVL

FROM

EMP E

CONNECT

PRIOR E.MGR

E.EMPNO

START

WITH

E.EMPNO

7876

ORDER

LEVEL

DESC

)

SELECT

A.LVL 最高等级加1,

LEVEL

当前等级,

A.LVL

LEVEL

优化后等级,

FROM

EMP E CONNECT

PRIOR E.MGR

E.EMPNO START

WITH

E.EMPNO

7876

ORDER

LEVEL

DESC

</pre>

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

查找员工编号为7839的所有下属(7839为king):

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

SELECT

LEVEL

等级, E.

FROM

EMP E

CONNECT

PRIOR E.EMPNO

E.MGR

START

WITH

E.EMPNO

7839

</pre>

</div>

--构造整个的层次结构

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

select

lpad(

level

)

ename ename, empno, mgr

from

emp

START

WITH

MGR

NULL

CONNECT

PRIOR EMPNO

MGR

</pre>

</div>

So, KING is the start with set then JONES BLAKE and CLARK fall under him.

Each of them 

becomes the 

PRIOR record in turn and their trees are expanded.

使用Connect By 结合 level构造虚拟行:

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

SELECT

LEVEL

FROM

DUAL CONNECT

LEVEL

</pre>

</div>

使用rownum实现类似的功能:

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

SELECT

LEVEL

FROM

DUAL CONNECT

LEVEL

</pre>

</div>

---------------------待续-----------------------

使用UNION ALL构造两层节点的树:

视图如下所示:

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

CREATE

REPLACE

VIEW

TREE_VIEW

SELECT

rootnodeid,

xxxx有限责任公司

treename,

-1

parent_id

FROM

dual

UNION

SELECT

to_char(d.deptno),

d.dname

d.loc,

parent_id

FROM

dept d;

</pre>

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

查询语句:

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

SELECT

LEVEL

FROM

TREE_VIEW T

START

WITH

T.PARENT_ID

-1

CONNECT

PRIOR T.ROOTNODEID

T.PARENT_ID

</pre>

</div>

-----以下为更新内容:

1、先查看总共有几个等级：

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

SELECT

COUNT

(

LEVEL

)

FROM

EMP E

CONNECT

PRIOR E.EMPNO

E.MGR

START

WITH

E.MGR

NULL

;

< /pre>

</div>

2、查看每个等级的人数。主要是通过LEVEL进行GROUP BY

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

SELECT

COUNT

(

LEVEL

)

FROM

EMP E

CONNECT

PRIOR E.EMPNO

E.MGR

START

WITH

E.MGR

NULL

GROUP

LEVEL

;

< /pre>

</div>

3、Oracle 10g提供了一个简单的connect_by_isleaf=1,

0表示非叶子节点

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

SELECT

LEVEL

等级, CONNECT_BY_ISLEAF

是否是叶子节点, E.

FROM

EMP E

CONNECT

PRIOR E.EMPNO

E.MGR

START

WITH

E.MGR

NULL

</pre>

</div>

4、SYS_CONNECT_BY_PATH

Oracle 9i提供了sys_connect_by_path(column, char), 其中column是字符型或能自动转

换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

SELECT

LEVEL

等级,

CONNECT_BY_ISLEAF

是否是叶子节点,

LPAD(

LEVEL

)

SYS_CONNECT_BY_PATH(ENAME,

)

FROM

EMP E

CONNECT

PRIOR E.EMPNO

E.MGR

START

WITH

E.MGR

NULL

;

</pre>

</div>

5、修剪树枝和节点:

    过滤掉编号是7566的数据(修剪节点)，他指的是把这个节点给裁掉，但是并没有破坏树结构，它的子节点还是可以正常的显示。

<div style='margin: 5px 0px;

padding: 5px;

border: 1px solid rgb(204, 204, 204);

overflow: auto;

font - family: "Courier New";

font - size: 12px;

background - color: rgb(245, 245, 245);

' class="cnblogs_code">

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

<pre style='font - family: "Courier New";

margin - top: 0px;

margin - bottom: 0px;

white - space: pre - wrap;

word - wrap: break - word;

SELECT

LEVEL

等级,

CONNECT_BY_ISLEAF

是否是叶子节点,

LPAD(

LEVEL

)

SYS_CONNECT_BY_PATH(ENAME,

FROM

EMP E

WHERE

e.empno

7566

CONNECT

PRIOR E.EMPNO

E.MGR

START

WITH

E.MGR

NULL

;

</pre>

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

裁掉编号是7698的节点和它的子节点:

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

SELECT

LEVEL

等级,

CONNECT_BY_ISLEAF

是否是叶子节点,

LPAD(

LEVEL

)

SYS_CONNECT_BY_PATH(ENAME,

= >

FROM

EMP E

CONNECT

PRIOR E.EMPNO

E.MGR

AND

E.EMPNO

7698

START

WITH

E.MGR

NULL

;

< /pre>

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

<p style="margin: 10px auto;

<img style="border: 0px currentColor;

" alt="" src="http: //image60.360doc.com/DownloadImg/2013/04/2216/31821006_15.png">

6、CONNECT_BY_ROOT的使用，oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。

<div style='margin: 5px 0px; padding: 5px; border: 1px solid rgb(204, 204, 204); overflow: auto; font-family: "Courier New"; font-size: 12px; background-color: rgb(245, 245, 245);'

class="cnblogs_code">

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

SELECT

LEVEL

等级,

CONNECT_BY_ISLEAF

是否是叶子节点,

CONNECT_BY_ROOT ENAME,

LPAD(

LEVEL

)

SYS_CONNECT_BY_PATH(ENAME,

FROM

EMP E

CONNECT

PRIOR E.EMPNO

E.MGR

START

WITH

E.MGR

NULL

;

</pre>

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name 排序，这样破坏了层次，比如特别关注某行的深度，按level 排序，也是会破坏层次的。在oracle10g中，增加了siblings

关键字的排序。

语法：order siblings by <expre>

它会保护层次，并且在每个等级中按expre排序。

<div style='margin: 5px 0px;

padding: 5px;

border: 1px solid rgb(204, 204, 204);

overflow: auto;

font - family: "Courier New";

font - size: 12px;

background - color: rgb(245, 245, 245);

' class="cnblogs_code">

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

<pre style='font - family: "Courier New";

margin - top: 0px;

margin - bottom: 0px;

white - space: pre - wrap;

word - wrap: break - word;

SELECT

LEVEL

等级,

CONNECT_BY_ISLEAF

是否是叶子节点,

LPAD(

LEVEL

)

SYS_CONNECT_BY_PATH(ENAME,

FROM

EMP E

CONNECT

PRIOR E.EMPNO

E.MGR

START

WITH

E.MGR

NULL

ORDER

SIBLINGS

E.ENAME;

< /pre>

<a style="border: currentColor !important; color: rgb(26, 139, 200); text-decoration: none;"

title="复制代码" href="">

</a>

</div>

<p style="margin: 10px auto;

<img style="border: 0px currentColor;

" alt="" src="http: //image60.360doc.com/DownloadImg/2013/04/2216/31821006_17.png">

connect_by_iscycle(存在循环，将返回1，否则返回0)

</h3>

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a

child which is also its ancestor. Otherwise it returns 0. 

You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE

parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the

results of a query that would otherwise fail because of a CONNECT BY loop

in the data.

</div>

<div style="color: rgb(75, 75, 75); line-height: 19px; font-family: Verdana, Geneva, Arial, Helvetica, sans-serif; font-size: 13px; margin-top: 10px;"

id="MySignature">

I believe that we are who we choose to be. Nobody‘s going to come and

save you, you‘ve got to save yourself. 我相信我们成为怎样的人是我们自己的选择。没有人会来拯救你，你必须要自己拯救自己。

</div>

< /td>

</tr>

</tbody>

</table>

</div>

<table>

<tbody>

<tr>

<td>

</td>

</tr>

</tbody>

</table>

</td>

</tr>

<tr>

来自：

风中的眼睛_

< /a>

  & gt; & nbsp;

<a href="http://www.360doc.com/userhome.aspx?userid=11947209&cid=3"

target="_blank">

《数据库函数》

< /a>

</div>

</a>

</div>

isfollow(11947209); < /script>

</div > </div>

<div>

<ul>

</div > <div id = "nextart" > 下一篇： < a onclick = "wzhitnew(58);"href = "http://www.360doc.com/content/13/0426/15/11947209_281077725.shtml"target = "_blank" > Oracle中rownum与rowid的理解 < /a></div > </ul>

</div > </div>

</td > </tr>

<tr>

/*文章页正文下方标签云，2013-8-21*/

var cpro_id = "u1350774 ";

</script>

</script>

frameBorder="0" width="650" allowTransparency="true" marginWidth="0" scrolling="no"

align="center,center">

</iframe>

</div>

</script>

< /div>

</td>

</tr>

<tr>

<tbody>

<tr>

</td>

</a>

</div>

<div style="margin-left: 10px; cursor: pointer;" id="sendflowerdiv" class="lf360 xhmainbox"

onmouseover="ChangeFlowerBtnStyle(1);" onmouseout="ChangeFlowerBtnStyle(2);"

onclick='Showflowerlayer("sendedLayer1");wzhitnew(27)'>

</div>

献花(

)

</div>

</td>

<td style="font - size: 12px; vertical - align: bottom;

" width="197 ">

<table style="vertical - align: text - bottom;

" border="0 " cellSpacing="0 " cellPadding="0 ">

<tbody>

<tr>

<td>

<div style="width: 126px; height: 20px;

<div style="float: left;

<span style="color: rgb(149, 149, 149); line - height: 16px; font - size: 12px;

</div>

<div style="width: 22px; float: left;

<span onclick="shareWeixin(); wzhitnew(64);

<a title="分享到微信" href="javascript: void(0);

</a>

</div>

</a>

</a>

</a>

</div>

</td>

<a>

<img style="cursor: pointer;" onclick="setTimeout(showShareLayer2, 10);wzhitnew(34);"

src="http://pubimage.360doc.com/wz/tb10.gif">

</a>

<div style="margin-top: 5px; margin-left: -100px; display: none; z-index: 1000;"

id="fenxiangLayer1" class="fenxiang">

<table style="background-color: rgb(255, 255, 255);" cellSpacing="0" cellPadding="0"

width="108">

<tbody>

<tr>

</td>

    人人网

</a>

</td>

</tr>

<tr>

</td>

    开心网

</a>

</td>

</tr>

<tr>

</td>

    搜狐微博

</a>

</td>

</tr>

<tr>

</td>

    推荐给朋友

</div>

</a>

</td>

</tr>

<tr>

</td>

</div>

</a>

</td>

</tr>

</tbody>

</table>

</div>

</td>

</tr>

</tbody>

</table>

</div>

</div>

< /td>

</tr>

</tbody>

</table>

</div>

</td>

</tr>

<tr>

</td>

</tr>

</tbody>

</table>

分享到：

mx:ComboBox | 关于oracle select distinct order by的问 ...

2014-03-27 13:32
浏览 563
评论(0)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论