CONAN ZONE
你越挣扎我就越兴奋
BlogJava
首页
新随笔
联系
聚合
管理
0 Posts :: 282 Stories :: 0 Comments :: 0 Trackbacks
留言簿
(6)
给我留言
查看公开留言
查看私人留言
文章分类
(325)
Application Server(4)
CVS(4)
Database(7)
Eclipse(7)
ExtJs(1)
GlassFish(1)
Hibernate(3)
HTML/CSS(3)
Italian(1)
J2EE(24)
J2SE(15)
JAVA(27)
JMS(2)
JQuery(7)
JS(12)
JVM(5)
Linux/Unix(21)
Mylyn(2)
NetBeans(4)
NoSql(1)
Oracle(27)
PowerDesigner(1)
Python(1)
Solr(15)
Spring(11)
SQL(13)
Struts(2)
Struts2/Webwork(12)
SVN(7)
VIM(13)
Web Service(13)
web技术(4)
其他技术(13)
养生保健(7)
动漫(1)
外语(2)
新闻娱乐(19)
旅游(1)
模式(1)
模板(2)
生活(2)
项目管理(7)
文章档案
(282)
2012年6月 (3)
2012年5月 (12)
2011年12月 (3)
2011年11月 (2)
2011年7月 (1)
2011年5月 (2)
2011年4月 (4)
2011年3月 (1)
2011年2月 (4)
2010年12月 (2)
2010年11月 (7)
2010年9月 (2)
2010年8月 (1)
2010年7月 (3)
2010年5月 (2)
2010年4月 (3)
2010年3月 (2)
2009年12月 (1)
2009年11月 (1)
2009年10月 (3)
2009年9月 (3)
2009年8月 (6)
2009年7月 (6)
2009年6月 (3)
2009年5月 (2)
2009年4月 (8)
2009年3月 (1)
2009年2月 (15)
2009年1月 (9)
2008年12月 (2)
2008年11月 (11)
2008年10月 (1)
2008年9月 (2)
2008年8月 (3)
2008年7月 (54)
2008年6月 (97)
guy's blog
ccsoft's blog
Ray's blog
square's blog
刘佳's blog
搜索
积分与排名
积分 - 406143
排名 - 136
最新评论
Oracle开发专题之:级联查询(Hierarchical Queries) 进阶应用:伪列Level
目录:
1.使用伪列Level显示表中节点的层次关系
2.统计表中节点的层数
3.统计表中各个层次的节点数量
4.查找表中各个层次的节点信息
5.在Start with中使用子查询
6.判断节点和节点之间是否具有层次关系
7.删除级联表中的子树
一、使用伪列Level显示表中节点的层次关系:
Oracle9i对级联查询的支持不仅在于提供了像Start with...Connect by这样的子句供我们很方便地执行查询,而且还提供了一个伪列(Pseudocolumn): Level。这个伪列的作用是在递归查询的结果中用来表示节点在整个结构中所处的层次。下面我们来看看实际的例子:
还是上次那个employee表,现在我们要在上次的需求上面增加点小玩意:输出每个节点的层次值,看如下SQL:
SQL
>
select
level
, id, emp_name, manager_id
from
employee start
with
id
=
2
connect
by
prior id
=
ma
nager_id
order
by
id;
LEVEL
ID EMP_NAME MANAGER_ID
--
-------- ---------- -------------------- ----------
1
2
mark
1
2
4
tom
2
2
5
paul
2
3
7
ben
4
SQL
>
我们可以看到在LEVEL列,输出了1,2,2,3的值,这就是Oracle为我们提供的一个伪列。此伪列只能用在start with...connect by子句中,下面我们来看另一种方式是否可行:
SQL
>
select
level
, p.
*
from
(
select
*
from
employee start
with
id
=
2
connect
by
prior id
=
manager_
id
order
by
id) p;
LEVEL
ID EMP_NAME MANAGER_ID
--
-------- ---------- -------------------- ----------
0
2
mark
1
0
4
tom
2
0
5
paul
2
0
7
ben
4
SQL
>
可以看到Level列的值全部变成了0,可见在这里Oracle并不认为虚表P里面的数据是“层次关系”,因而对于Level都返回0
二、统计表中节点的层数:
假设现在我们想看一下当前employee表中员工总共分为几个级别,我们应该如何做呢?请看下面的SQL
SQL
>
select
*
from
employee;
ID EMP_NAME MANAGER_ID
--
-------- -------------------- ----------
1
king
2
mark
1
3
bob
1
4
tom
2
5
paul
2
6
jack
3
7
ben
4
7
rows selected.
SQL
>
SQL
>
SQL
>
select
count
(
level
)
from
employee start
with
manager_id
is
null
connect
by
prior id
=
manager_i
d;
COUNT
(
LEVEL
)
--
----------
7
SQL
>
SQL
>
select
count
(
distinct
level
)
from
employee start
with
manager_id
is
null
connect
by
prior id
=
manager_id;
COUNT
(DISTINCTLEVEL)
--
------------------
4
从这里我们可以看到,在统计的时候一定要使用
distinct
关键字,否则得到的错误的结果。
三、统计表中各个层次的节点数量:
假设我们想知道employee表中每个级别的员工数量,我们应该如何做呢--对了,使用Level和group by子句了
SQL
>
select
level
,
count
(
level
)
from
employee start
with
manager_id
is
null
connect
by
prior id
=
ma
nager_id
group
by
level
;
LEVEL
COUNT
(
LEVEL
)
--
-------- ------------
1
1
2
2
3
3
4
1
四、查找表中各个层次的节点信息:
上面的例子很简单,我们看到Level可以用在group by子句中,现在我们更进一步,查看指定层次的员工信息,比如说我现在打算查看Level=2的所有员工的记录,应该如何做呢?很自然地我们想到了第一个SQL语句:
SQL
>
select
level
, id, emp_name, manager_id
from
employee
where
level
>=
2
;
no rows selected
很奇怪吧,这这里level关键字就不起作用了,这是因为level伪列只能在和start with...connect by子句结合时才能发挥作用,就想上面的统计各层节点数量一样,于是我们又立马想到了第二个SQL语句:
select
*
from
(
select
level
, id, emp_name, manager_id
from
employee
start
with
manager_id
is
null
connect
by
prior id
=
manager_id
order
by
id) p
where
p.
level
=
2
看起来这个句子没有什么问题吧,实际执行的效果如何呢?我们在SQL*PLUS下执行,结果却是报错:
ERROR at line
1
:
ORA
-
01747
: invalid
user
.
table
.
column
,
table
.
column
,
or
column
specification
很郁闷!为什么会报p.level不可识别呢?这是因为level是Oracle的伪列,并不属于任何一个表,我们必须使用别名把这个伪列“伪装”成一个实际的列,现在我们看第三个语句,注意语句高亮处。
SQL
>
select
*
2
from
(
select
level
emp_level
, id, emp_name, manager_id
3
from
employee
4
start
with
manager_id
is
null
5
connect
by
prior id
=
manager_id
6
order
by
id) p
7
where
p.emp_level
=
2
;
EMP_LEVEL ID EMP_NAME MANAGER_ID
--
-------- ---------- -------------------- ----------
2
2
mark
1
2
3
bob
1
SQL
>
这次终于搞定了!不过实际上我们有更简单的解决方法,请看第四个SQL语句:
SQL
>
select
level
, id, emp_name, manager_id
2
from
employee
3
where
level
=
2
4
start
with
manager_id
is
null
5
connect
by
prior id
=
manager_id
6
order
by
id;
LEVEL
ID EMP_NAME MANAGER_ID
--
-------- ---------- -------------------- ----------
2
2
mark
1
2
3
bob
1
上面我们是查看某个层次的所有节点信息,现在我们打算看看所有层次的节点信息,而且要求用一种直观的信息显示出来。下面的例子演示了如何使用空格缩进的方式来直观显示节点之间的层次关系:
SQL
>
select
level
, id, lpad(
'
'
,
2
*
(
level
-
1
))
||
emp_name name, manager_id
2
from
employee
3
start
with
manager_id
is
null
4
connect
by
prior id
=
manager_id;
LEVEL
ID NAME MANAGER_ID
--
-------- ---------- -------------------- ----------
1
1
king
2
2
mark
1
3
4
tom
2
4
7
ben
4
3
5
paul
2
2
3
bob
1
3
6
jack
3
7
rows selected.
请注意这里的lpad函数的作用,正是它利用了层次和空格进行缩进,让我们可以很直观地从NAME字段对齐方式就知道各个节点的层次关系。如果我们需要过滤其中的某些节点,只需要将where条件加在start with
前面
就可以了(注意必须是前面,否则会报语法错误)。
五、在Start with中使用子查询:
在前面我们看到的例子中,start with的值都是一个固定的内容,但有些时候查询的起始点并不容易确定,比如:查询工号最小的员工节点及其子节点,这个时候工号最小很明显是一个查询的条件,需要我们先通过执行一个查询得到确定的值,再作为查询的起点。请看例子:
SQL
>
select
level
, id, lpad(
'
'
,
2
*
(
level
-
1
))
||
emp_name name, manager_id
2
from
employee
3
start
with
id
=
(
select
min
(id)
from
employee)
4
connect
by
prior id
=
manager_id;
LEVEL
ID NAME MANAGER_ID
--
-------- ---------- -------------------- ----------
1
1
king
2
2
mark
1
3
4
tom
2
4
7
ben
4
3
5
paul
2
2
3
bob
1
3
6
jack
3
7
rows selected.
六、判断节点和节点之间是否具有层次关系:
在日常工作中除了查询节点的信息之外,另一个常见的应用就是判断某个节点和另外一个/些节点之间是否具有层次关系。例如我想知道员工mark是不是员工jack的领导(直接或间接的都可以),我应该怎么做呢?
考虑到start with...connect by会返回一棵节点树,假如节点数上没有jack节点,那么说明mark并不是jack的直接或间接领导,如果找到那说明mark是jack的父节点。方法简单
SQL
>
select
level
,
2
id,
3
lpad(
'
'
,
2
*
(
level
-
1
))
||
emp_name employee_name,
4
manager_id
5
from
employee
6
where
emp_name
=
'
jack
'
7
start
with
emp_name
=
'
mark
'
8
connect
by
prior id
=
manager_id;
no rows selected
七、删除级联表中的子树:
假设现在employee表中的mark及其下属员工离职,那么我们为了维护数据的完整性,必须将mark及其下属员工的节点都删除,有了start with...connect by和level我们就可以轻松地做到这一点了。
【1】按名称删除节点树:
SQL
>
delete
from
employee
2
where
id
in
(
select
id
3
from
employee
4
start
with
emp_name
=
'
mark
'
5
connect
by
prior id
=
manager_id);
4
rows deleted.
【2】按层次删除节点树:
posted on 2008-06-22 18:49
CONAN
阅读(239)
评论(0)
编辑
收藏
所属分类:
SQL
、
Oracle
Powered by:
BlogJava
Copyright © CONAN