ITPub博客

首页 > 应用开发 > IT综合 > 家族树和connect by子句

家族树和connect by子句

原创 IT综合 作者:mfm088 时间:2007-05-12 16:57:25 0 删除 编辑
家族树和connect by子句

Oracle的一个非常有趣但又很少使用或了解的功能是connect by子句。简单地说,它是报告家族树分支排序的一种方法。这样的树经常遇到:人类家庭、牲畜、马、社团管理、公司划分、制造、文学、概念、进化、科学研究、理论、甚至建立在视图上的视图。

[@more@]

家族树和connect by子句- -

家族树和connect by子句

Oracle的一个非常有趣但又很少使用或了解的功能是connect by子句。简单地说,它是报告家族树分支排序的一种方法。这样的树经常遇到:人类家庭、牲畜、马、社团管理、公司划分、制造、文学、概念、进化、科学研究、理论、甚至建立在视图上的视图。

在每棵树中,connect by提供了一个报告所有家族成员的方法。它允许排除一个家族的分枝或单个成员,也允许上、下遍历此树,以及报告在遍历过程中遇到的家族成员。

树中最早的祖先在技术上称为根节点(root node)。在日常英语中被称为树干(trunk)。从树干扩展出来的为树枝(branch),每一个树枝又有其他树枝。从一个较大的分枝分裂出一个或多个树枝的分叉(fork)称为节点(node)。树枝的最末端称为树叶(1eaf)或树叶节点(1eaf node)。图12-1显示了一棵树的图。

下面是在1900年1月至1908年10月出生的奶牛和公牛的列表。出生的每个后代同其性别、双亲及出生日期等信息同在表中作为一行。如果用图12-1的表来比较奶牛及其后代,可以发现它们是对应的。表中EVE没有双亲,因为它是第一代,ADAM和BANDIT由人工喂养,因而也无双亲。
column Cow format a6
column Bull format a6
column Offspring format a10
column Sex format a 3

select * from BREEDING
order by Birthdate;



OFFSPRING SEX COW BULL BIRTHDATE
____________________________________________________________________________________________

下面这个查询语句直观地说明了—个家族的关系。这是通过使用LPAD函数和一特殊的列、级,与connect by一起来完成的。Level是一个从1(EVE)到5(DELLA)的数字,实际上表示代(generation),DELLA就是第五代。无论何时使用connect by子句时, 这一Level列可被加入select语句中,从而找出每一行属于第几代。同SysDate

和User一样,level是一个伪列(pseudo co1umn)。它并不真是一个表的真实的一部分,但在特殊环境下可以使用。下面列出使用Level的一个例子。
这个查询的结果出现在下表中,但为什么select语句会得到这样的结果呢?它是怎样工作的呢?

select Cow,Bull,LPAD(' ',6*(Level-1))||Offspring Offspring,Sex,Birthdate
from BREEDING
start with Offspring='EVE'
connect by Cow=PRIOR Offspring;

COW BUlL OFFSPRING SEX BIRTHDATE

--------------------------------------------------------------

EVE FEVE ADAM BETSY F 02_JAN_00
BETSY ADAM NOVI F 30_MAR_03
BETSY BANDIT GINNY F 04_DEC_03
GINNY DUKE SUZY F 03_APR_06
SUZY BANDIT DELLA F 11_OCT_08
GINNY DUKE RUTH F 25_DEC_06
BETSY BANDIT TEDDI F 12_AUG_05
EVE ADAM POCO M 15_JUL_00
EVE BANDIT GRETA F 12_MAR_01
EVE POCO MANDY F 22_AUG_02
MANDY BANDIT DUKE M 24_JUL_04
MANOY POCO PAULA F 21_DEC_06
EVE POCO CINDY F 09_FEB_03

注意,这实际上是图12-l按顺时针方向转到一边。EVE不在中心,但她是此树的根节点(trunk)。她的孩子有BETSY、POCO、GRETA、MANDY及CINDY。BETSY的孩子有NOVL、GINNY及TEDDI。GINNY的孩子有SU2Y和RUTH。SUZY的孩子有DELLA。MANDY也有两个孩子,它们是DUKE和PAULA。

这棵树从EVE开始作为第一代。如果SQL语句指定从MANDY开始,那么仅有MANDY、DUKE和PAULA被选出。start with定义了树开始显示的起点位置,且仅包括从start with指明的双亲分叉的树枝。startwith的作用正如它的名字所示。

select语句中的LPAD的结构很可能令人感到困惑。回顾一下第6章中LPAD的格式:

LPAD(string,length[,'set'])

比较一下这里与先前select语句中LPAD的区别:

LPAD(' ',6*(Level-1))

由空格分开的两个单引号没有定义用在填充中的字符,它是一个常量列,一个字符长的串。6*(Level-1)是长度(1ength),因为set没有定义,使用缺省值,缺省值也为一空格。换句话说,它告诉SQL“定义一个字符空间,并用6*(Level-1)个空格从左边填充它”。那么这个数是什么呢?

6*(Level-1)

上面的算式首先从Level中减1。EVE的级数为1,所以1-1等于0。对于BETSY。它的Level(它的代数)为2,因此2-1为1。结果乘以6,这个数字表示与“左边”Offspring列连接的空格数。注意LPAD不直接填充Offspring,但与offspring并置。结果所显示的效果是明显的。每一个后代(generation)或级(1evel)左边按它的Level填充相应数目的空格。

为什么要填充和并置而不简单地直接在Offspring上使用LPAD呢?有两个原因:首先,在Offspring上直接使用LPAD将使得名字右对齐。每一级的名称将它们的最后字母的垂直排列起来。其次,如果Level-1等于零,如EVE,EVF使用LPAD的结果将是零字符宽。EVE将消失:

select Cow,Bull,LPAD(Offspring,6*(Level-1),' ') Offspring, Sex,Birthdate from BREEDING
start with Offspring='EVE'
connect by Cow=PRIOR Offspring;

COW BUlL OFFSPRING SEX BIRTHDATE

--------------------------------------------------------------

FEVE ADAM BETSY F 02_JAN_00
BETSY ADAM NOVI F 30_MAR_03
BETSY BANDIT GINNY F 04_DEC_03
GINNY DUKE SUZY F 03_APR_06
SUZY BANDIT DELLA F 11_OCT_08
GINNY DUKE RUTH F 25_DEC_06
BETSY BANDIT TEDDI F 12_AUG_05
EVE ADAM POCO M 15_JUL_00
EVE BANDIT GRETA F 12_MAR_01
EVE POCO MANDY F 22_AUG_02
MANDY BANDIT DUKE M 24_JUL_04
MANOY POCO PAULA F 21_DEC_06
EVE POCO CINDY F 09_FEB_03

这样,为了得到每一级合适的位置,保证EVE显示出来,使得名字在左边垂直排列,应该和并置函数一起使用LPAD,而不是直接对Offspring列操作。

现在,connect by是怎样工作的呢?再看一下图12-1。从NOVI开始,向下遍历,哪一头奶牛是先于NOVI的一代(Offspring)?首先是BETSY。先于BETSY的一代则是EVE。尽管以下这个子句不是马上可以看懂的,这个子句是:

connect by Cow=PRIOR Offspring

它告诉SQL找到cow列中其值等于前行中的Offspring列的值的下一行。看一下表,读者就会发现这是正确的。

6.1 排除单一体及分枝

有两种方法可从一个报表中排除奶牛。一个是用通常的where子句技术,另一种是使用connect by子句。不同点在于使用connect by子句排除的不只是提到的奶牛,而且还包括它们所有的孩子。如果用connect by排除BETSY,那么NOVI、GINNY、TEDDI、SUZY、RUTH及DELLA都被排除。connect by实际上遍历树的结构。如果BETSY还没有出生,那么它的后代也就不会有,这个例子中,用and句子修改

connect by子句:

select Cow,Bull,LPAD(’’,6*(Level-1))||Offspring Offspring,Sex,Birthdate from BREEDING
start with Offspring='EVE’
connect by Cow=PRIOR Offspring
and Offspring!='BETSY';

COW BUlL OFFSPRING SEX BIRTHDATE

---------------------------------------------------------------

EVE F
BETSY ADAM NOVI F 30_MAR_03
BETSY BANDIT GINNY F 04_DEC_03
BETSY BANDIT TEDDI F 12_AUG_05
EVE ADAM PETSY F 02_JAN_00
EVE ADAM POCO M 15_JUL_00
EVE BANDIT GRETA F 12_MAR_01
EVE POCO MANDY F 22_AUG_02
EVE POCO CINDY F 09_FEB_03
GINNY DUKE SUZY F 03_APR_06
GINNY DUKE RUTH F 25_DEC_06
MANDY BANDIT DUKE M 24_JUL_04
MANOY POCO PAULA F 21_DEC_06SUZY BANDIT DELLA F 11_OCT_08

代(generations)是显而易见的,但Offspring和它们的母亲密

切地组合在一起。另一个查看相同家族树的方法是按生日,如下所示:

select Cow,Bull,LPAD(' ',6*(Level-1))||OffSpring OffSpring,Sex,Birthdate
from BREEDING
start with Offspring='EVE'
connect by Cow=PRIOR OffSpring Order by Binhdate;

COW BUlL OFFSPRING SEX BIRTHDATE

---------------------------------------------------------------

EVE F
EVE ADAM BETSY F 02_JAN_00
EVE ADAM POCO M 15_JUL_00
EVE BANDIT GRETA F 12_MAR_01
EVE POCO MANDY F 22_AUG_02
EVE POCO CINDY F 09_FEB_03
BETSY ADAM NOVI F 30_MAR_03
BETSY BANDIT GINNY F 04_DEC_03
MANDY BANDIT DUKE M 24_JUL_04
BETSY BANDIT TEDDI F 12_AUG_05
GINNY DUKE SUZY F 03_APR_06
MANDY POCO PAULA F 21_DEC_06
GINNY DUKE RUTH F 25_DEC_06
SUZY BANDIT DELLA F 11_OCT_08

现在行的顺序不像在树型结构中,不再表示代,但作为一棵树,仍然保留着这些信息。要了解哪个后代属于哪些双亲,就要查看Cow和Bull列。

6.2 遍历至根

到现在, 对家族树报表的遍历可以从父母到孩子。从一个孩子开始,移回到双亲、祖双亲、曾祖双亲等等,这可能吗?为了这样做,PRIOR将被移动到等号的另一边。下面跟踪DELLA的祖先:

select Cow,Bull,LPAD(' ',6*(Level-1))|| OffSpring OffSphng,
Sex,Birthdate
from BREEDING
start With OffSpring='DELLA’
connect by OffSpring=PRIOR Cow;

COW BUlL OFFSPRING SEX BIRTHDATE

---------------------------------------------------------------

SUZY BANDIT DELLA F 11_OCT_08
GINNY DUKE SUZY F 03_APR_06
BETSY BANDIT GINNY F 04_DEC_03
EVE ADAM BETSY F 02_JAN_00
EVE F
这里显示了DELLA的根,但同先前显示的相比较,可能有些令人迷惑。这看起来DElLA是祖先,EVE是曾曾孙女。增加一个按生日排序的order by,但EVE仍在最右边:

select Cow,Bull,LPAD(' ',6*(Level-1))||OffSpring Offspring,Sex,Birthdate
from BREEDlNG
start with Offspring='DELLA’
connect by OffSpring=PRIOR Cow
order by Birthdate;

COW BUlL OFFSPRING SEX BIRTHDATE

---------------------------------------------------------------

EVE F
EVE ADAM BETSY F 25_DEC_06
BETSY BANDIT GINNY F 04_DEC_03
GINNY DUKE SUZY F 02_JAN_00
SUZY BANDIT DELLA F 11_OCT_08

解决的办法是简单地改变LPAD中的计算方法:select Cow,Bull,LPAD(' ',6*(5-Level))|| OffSpring Offspring,Sex,Birthdate
from BREEDlNG
start with Offspring='DELLA’
connect by OffSpring=PRIOR Cow
order by Birthdate;

COW BUlL OFFSPRING SEX BIRTHDATE

---------------------------------------------------------------

EVE F
EVE ADAM BETSY F 02_JAN_00
BETSY BANDIT GINNY F 04_DEC_03
GINNY DUCK SUZY F 03_APR_06
SUZY BANDIT DELLA F 11_OCT_08

最后,可以看出当用connect by跟踪公牛的双亲时,报表的变化是相当大的!下面是Adam的后代:

select Cow,Bull,LPAD(' ',6*(Level-1))|| OffSpring Offspring,Sex,Birthdate
from BREEDlNG
start with Offspring='ADAM’
connect by PRIOR OffSpring=Bull;

COW BUlL OFFSPRING SEX BIRTHDATE

---------------------------------------------------------------

ADAM M
EVE ADAM BETSY F 02_JAN_00
EVE ADAM POCO M 15_JUL_00
EVE POCO MANDY F 22_AUG_02
EVE POCO CINDY F 09_FEB_03
MANDY POCO PAULA F 21_DEC_06
BETSY ADAM NOVI F 30_MAR_03

ADAM和BANDIT是牛群中最初的公牛。为了创建报告ADAM和BANDIT后代的单一树,必须为它们俩编造一个“父亲”,这个“父亲”可能是这棵树的根。这些覆盖前面介绍过的树类型的替换树的一个优点是,从家庭到项目到公司内部部门的分配,能够以多种方法正确地描述很多种继承组。

select Cow,Bull,LPAD(' ',6*(Level-1))|| OffSpring Offspring,Sex,Birthdate
from BREEDlNG
start with Offspring='BANDIT’
connect by PRIOR OffSpring=Bull;

COW BUlL OFFSPRING SEX BIRTHDATE

---------------------------------------------------------------

BANDIT M
EVE BANDIT GRETA F 12_MAR_01
BETSY BANDIT GINNY F 04_DEC_03
MANDY BANDIT DUKE M 24_JUL_04
GINNY DUKE SUZY F 03_APR_06
GINNY DUKE RUTH F 25_DEC_06
BETSY BANDIT TEDDI F 12_AUG_05
SUZY BANDIT DELLA F 11_OCT_08

6.3 基本规则

用connect by及start with建立树结构的报表并不难,但必须遵守下列规则:

.使用connect by时子句的顺序是

select
from
where
start with
connect by
order by

.prior使报表从树根到树叶(如果prior列是父母),或从树叶至树根(如果prior列是孩子)。

.where子句将从树中去掉单个节点,但保留她它们的后代(或祖先,假设PRIOR在等号的右边)。

.connect by中的限定(尤其是不等时)将去掉单个节点及其后代(或祖先,取决于浏览树的方向)。

.connect by不能用在where子句的连接表(table join)中。

这组特别的命令集只有少数人可能正确地记住。然而,有了对树及继承性的基本理解,在构造一个正确的select语句来报告一棵树时就不是一难事,可以参考本章来得到正确的语法。

 

 

 

 

 


 

 

EVE         F 
ADAM        M 
BANDIT      M 
BETSY       F      EVE      ADAM      02-JAN-00 
POCO        M      EVE      ADAM      15-JUL-00 
GRETA       F      EVE      BANDIT    12-MAR-01
MANDY       F      EVE      POCO      22-AUG-02 
CINDY       F      EVE      POCO      09-FEB-03 
NOVI        F      BETSY    ADAM      30-MAR-03 
GINNY       F      BETSY    BANDIT    04-DEC-03 
DUKE        M      MANDY    BANDIT    24-JUL_04 
TEDDI       F      BETSY    BANDIT    12_AUG_05 
SUZY        F      GINNY    DUKE      03_APR_06 
PAULA       F      MANDY    POCO      21_DEC_06 
RUTH        F      GINNY    DUKE      25_DEC_06 
DELLA       F      SUZY     BANDIT    11_OCT_08 
注:
关于双树的情况,比如person(树A),unit(树B)
树A属于树B
这时候涉及联合检索,例如:
select b.unit_level from person a,(select LEVEL as unit_level from unit start with unit_parent is null 
connect by UNIT_CODE=PRIOR UNIT_PARENT_CODE) b
where a.UNIT_CODE = b.UNIT_CODE
connect by PERSON_PARENT_CODE =PRIOR PERSON_CODE
start with PERSON_PARENT_CODE is null order siblings by b.unit_level, a.CODE
如果你有其他问题,欢迎探讨。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/330796/viewspace-914783/,如需转载,请注明出处,否则将追究法律责任。

上一篇: RAID 技术详解
全部评论

注册时间:2012-01-14

  • 博文量
    98
  • 访问量
    1025618