家族树和connect by子句

最新推荐文章于 2021-04-07 02:46:15 发布

cuipeng3714

最新推荐文章于 2021-04-07 02:46:15 发布

阅读量204

点赞数

家族树和connect by子句

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

[@more@]

家族树和connect by子句- -

家族树和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_04MANOY 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_06SUZY 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_00SUZY 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_06SUZY 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/330796/viewspace-914783/

cuipeng3714

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
家族树和connect by子句

家族树和connect by子句 Oracle的一个非常有趣但又很少使用或了解的功能是connect by子句。简单地说，它是报告家族树分支排序的一种方法。这样的树经常遇到：人类家庭、牲畜、马、社团管理、公司划分、制造...
复制链接

扫一扫