level 和 connect by ,start with 合在一起,一般是用来实现递归(树)查询。
connect by 子句定义表中的数据是如何相互联系的
start with 子句定义树查询的初始起点
PRIOR 和 START WITH 关键字是可选项
PRIORY 运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系, PRIOR
运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构是的顺序是自顶向下还是
自底向上。在连接关系中,除了可以使用列名外,还允许使用列表达式。 START WITH 子句为
可选项,用来标识哪个节点作为查找树型结构的根节点。若该子句被省略,则表示所有满足查询
条件的行作为根节点。
《Oracle database 11g完全参考手册》十四章中的例子:
SQL> col Cow format a6;
SQL> col Bull format a6;
SQL> col Offspring format a30;
SQL> col
Sex format a3;
SQL> select * from BREEDING order by Birthdate;
OFFSPRING
SEX COW
BULL BIRTHDATE
------------------------------ --- ------ ------ ------------
BETSY
F
EVE
ADAM
02-1月 -00
POCO
M
EVE
ADAM
15-7月 -00
GRETA
F
EVE
BANDIT 12-3月 -01
MANDY
F
EVE
POCO
22-8月 -02
CINDY
F
EVE
POCO
09-2月 -03
NOVI
F
BETSY
ADAM
30-3月 -03
GINNY
F
BETSY
BANDIT 04-12月-03
DUKE
M
MANDY
BANDIT 24-7月 -04
TEDDI
F
BETSY
BANDIT 12-8月 -05
SUZY
F
GINNY
DUKE
03-4月 -06
PAULA
F
MANDY
POCO
21-12月-06
OFFSPRING
SEX COW
BULL BIRTHDATE
------------------------------ --- ------ ------ ------------
RUTH
F
GINNY
DUKE
25-12月-06
DELLA
F
SUZY
BANDIT 11-10月-08
ADAM
M
EVE
F
BANDIT
M
已选择16行。
以下三个查询可清楚地看出继承关系:
以母牛为线索,EVE处于最根的位置
SQL> select Cow,Bull,LPAD(' ',6*(Level-1))||Offspring As Offspring,Sex,Birthdate
from BREEDING
start with Offspring = 'EVE'
connect by Cow = PRIOR Offspring;
COW
BULL
OFFSPRING
SEX BIRTHDATE
------ ------ ------------------------------ --- ------------
EVE
ADAM
BETSY
F
02-1月 -00
BETSY
BANDIT
GINNY
F
04-12月-03
GINNY
DUKE
RUTH
F
25-12月-06
GINNY
DUKE
SUZY
F
03-4月 -06
SUZY
BANDIT
DELLA
F
11-10月-08
BETSY
ADAM
NOVI
F
30-3月 -03
BETSY
BANDIT
TEDDI
F
12-8月 -05
EVE
POCO
CINDY
F
09-2月 -03
EVE
BANDIT
GRETA
F
12-3月 -01
EVE
POCO
MANDY
F
22-8月 -02
COW
BULL
OFFSPRING
SEX BIRTHDATE
------ ------ ------------------------------ --- ------------
MANDY
BANDIT
DUKE
M
24-7月 -04
MANDY
POCO
PAULA
F
21-12月-06
EVE
ADAM
POCO
M
15-7月 -00
已选择14行。
以公牛为线索,BANDIT和BETSY处于根的位置,它们两者没有继承关系
SQL> select Cow,Bull,LPAD(' ',6*(Level-1))||Offspring As Offspring,Sex,Birthdate from BREEDING start with Offspring = 'BANDIT' connect by BULL = PRIOR Offspring;
COW
BULL
OFFSPRING
SEX BIRTHDATE
------ ------ ------------------------------ --- ------------
SUZY
BANDIT
DELLA
F
11-10月-08
MANDY
BANDIT
DUKE
M
24-7月 -04
GINNY
DUKE
RUTH
F
25-12月-06
GINNY
DUKE
SUZY
F
03-4月 -06
BETSY
BANDIT
GINNY
F
04-12月-03
EVE
BANDIT
GRETA
F
12-3月 -01
BETSY
BANDIT
TEDDI
F
12-8月 -05
已选择8行。
SQL> select Cow,Bull,LPAD(' ',6*(Level-1))||Offspring As Offspring,Sex,Birthdate from BREEDING start with Offspring = 'BETSY' connect by Cow = PRIOR Offspring;
COW
BULL
OFFSPRING
SEX BIRTHDATE
------ ------ ------------------------------ --- ------------
EVE
ADAM
BETSY
F
02-1月 -00
BETSY
BANDIT
GINNY
F
04-12月-03
GINNY
DUKE
RUTH
F
25-12月-06
GINNY
DUKE
SUZY
F
03-4月 -06
SUZY
BANDIT
DELLA
F
11-10月-08
BETSY
ADAM
NOVI
F
30-3月 -03
BETSY
BANDIT
TEDDI
F
12-8月 -05
已选择7行。