Oracle Handbook系列之结构化查询(1)(2)

六)CONNECT BY与WHERE子句

下面再说说，关于引入结构化查询后，SQL语句的执行顺序问题，根据Oracle文档，先后是：

1)JOIN，无论用的是JOIN ON的写法，还是在WHERE中做的关联

2)CONNECT BY

3)其它的WHERE条件

看一个例子，假设上面的各位职工，需要保存一些注释信息，同时这些信息根据中文、英文分成两个不同版本，我们可以简单设计一下这个注释表：

|-Emp 职工编号 
|-Lang 语言（中文或英文） 
|-Emp_desc 职工的具体描述 
 
select emp, lang, emp_desc from t_desc; 
 
1     chinese 这是注释 
1     english   this is comment 
2     chinese 这是注释 
2     english   this is comment 
3     chinese 这是注释 
3     english   this is comment 
4     chinese 这是注释 
4     english   this is comment 
5     chinese 这是注释 
5     english   this is comment 
6     chinese 这是注释 
6     english   this is comment 

现在需要在原有的职工结构化查询中包括每个职工的中文注释信息，我们看看下面的查询：

select t.emp, t.mgr, td.emp_desc, LEVEL 
from t_hierarchical t, t_desc td 
where t.emp=td.emp and td.lang='chinese' 
START WITH t.emp=1 
CONNECT BY PRIOR t.emp=t.mgr; 
 
1            chinese 这是注释 1 
2     1     chinese 这是注释 2 
3     2     chinese 这是注释 3 
4     3     chinese 这是注释 4 
6     3     chinese 这是注释 4 
4     3     chinese 这是注释 4 
6     3     chinese 这是注释 4 
5     2     chinese 这是注释 3 
3     2     chinese 这是注释 3 
4     3     chinese 这是注释 4 
6     3     chinese 这是注释 4 
4     3     chinese 这是注释 4 
6     3     chinese 这是注释 4 
5     2     chinese 这是注释 3 
2     1     chinese 这是注释 2 
3     2     chinese 这是注释 3 
4     3     chinese 这是注释 4 
6     3     chinese 这是注释 4 
4     3     chinese 这是注释 4 
6     3     chinese 这是注释 4 
5     2     chinese 这是注释 3 
3     2     chinese 这是注释 3 
4     3     chinese 这是注释 4 
6     3     chinese 这是注释 4 
4     3     chinese 这是注释 4 
6     3     chinese 这是注释 4 
5     2     chinese 这是注释 3 

再看这个查询，看起来与前者是一样的：

select t.emp, t.mgr, td.emp_desc, LEVEL 
from t_hierarchical t join t_desc td 
on (t.emp=td.emp and td.lang='chinese') 
START WITH t.emp=1 
CONNECT BY PRIOR t.emp=t.mgr; 
 
1            这是注释 1 
2     1     这是注释 2 
3     2     这是注释 3 
4     3     这是注释 4 
6     3     这是注释 4 
5     2     这是注释 3 

第二个是我们期望的结果，第二个则相去甚远。追究原因，是因为前一个例子中第二个条件 td.lang=’chinese’不被认为是JOIN条件，所以在CONNECT BY之后执行；后一个例子中由于显式地把第二个条件写在了JOIN ON子句中，所以它在CONNECT BY之前执行。

由于缺少第二个条件的JOIN（即本节第一例）会导致每个的职工出现两次，换一个数据少一点的例子，看看CONNECT BY遇到这样的重复数据的时候是怎么处理的。

select emp, mgr, lang from t2; 
 
1            chinese 
1            english 
2     1     chinese 
2     1     english 

CONNECT BY之后：

select emp, mgr, lang from t2 
start with emp=1 
connect by prior emp=mgr; 
 
1            chinese 
2     1     chinese 
2     1     english 
1            english 
2     1     chinese 
2     1     english 

lang=’chinese’过滤之后：

1            chinese 
2     1     chinese 
2     1     chinese 

出现重复行，显然不是我们期望的结果。

七)CONNECT BY LEVEL

下面我再来看看一个特殊的用法 CONNECT BY LEVEL，这是一个理解起来令人头痛，但同时在某些情境下又是非常有用的：

select LEVEL from dual CONNECT BY LEVEL<=6; 
 
1 
2 
3 
4 
5 
6 

如果你以前从未使用过，但是不幸你猜中了结果，我深表佩服，我至今没有想通，事实上，它甚至不太符合结构化查询CONNECT BY的语法，因为根据Oracle文档，CONNECT BY条件中至少有一个表达式要使用PRIOR关键字。 以至于有人觉得CONNECT BY LEVEL是一个BUG，怀疑Oracle可能在后续的版本中加以纠正。

无论如何，CONNECT BY LEVEL在Oracle 10g/11g中运行良好，如果你不想费劲想通这其中的原由，可以简单地把想认为是构造了一个循环，因此如果你写成CONNECT BY 1=1，则会输出1到无穷大的数。

原文链接：http://www.cnblogs.com/KissKnife/archive/2011/02/25/1964816.html