将connect-by与递归with进行比较时，我注意到使用循环检测时结果集的差异。结果连接对我来说更直观，所以我想知道Oracle的实现是否包含错误，或者这是否是标准ANSI和预期行为。因此，我的问题是，如果您可以使用其他数据库(如MysqL，DB2，sql Server等)检查递归查询。如果这些数据库当然支持recursive with子句。
以下是它在Oracle 11.2.0.1.0上的工作原理
sql> select * 2 from t 3 / ID PARENT_ID ---------- ---------- 1 2 2 1 2 rows selected.
使用CONNECT BY语法的查询：
sql> select id 2,parent_id 3,connect_by_iscycle 4 from t 5 connect by nocycle parent_id = prior id 6 start with id = 1 7 / ID PARENT_ID CONNECT_BY_ISCYCLE ---------- ---------- ------------------ 1 2 0 2 1 1 2 rows selected.
这看起来很直观。但是，使用新的ANSI语法，它会再返回一行：
sql> with tr (id,parent_id) as 2 ( select id 3,parent_id 4 from t 5 where id = 1 6 union all 7 select t.id 8,t.parent_id 9 from t 10 join tr on t.parent_id = tr.id 11 ) cycle id set is_cycle to '1' default '0' 12 select id 13,parent_id 14,is_cycle 15 from tr 16 / ID PARENT_ID I ---------- ---------- - 1 2 0 2 1 0 1 2 1 3 rows selected.
这是您可以用来检查的脚本：
create table t
( id number,parent_id number
);
insert into t values (1,2);
insert into t values (2,1);
commit;
with tr (id,parent_id) as
( select id,parent_id
from t
where id = 1
union all
select t.id,t.parent_id
from t
join tr on t.parent_id = tr.id
) cycle id set is_cycle to '1' default '0'
select id,parent_id,is_cycle
from tr;
解决方法
CONNECT_BY_ISCYCLE的文档：
The
CONNECT_BY_ISCYCLEpseudocolumn returns1if the current row has a child which is also its ancestor
而在CYCLE：
A row is considered to form a cycle if one of its ancestor rows has the same values for the cycle columns.
在您的示例中，第2行确实有一个子节点，它也是它的祖先，但它的id尚未返回。
换句话说，CONNECT_BY_ISCYCLE检查子项(尚未返回)，而CYCLE检查当前行(已返回)。
CONNECT BY是基于行的，而递归CTE是基于集合的。
在递归CTE中没有“孩子”的概念。它是一个基于集合的操作，可以完全从树中产生结果。一般来说，锚点部分和递归部分甚至可以使用不同的表格。
由于递归CTE通常用于构建层次结构树，因此Oracle决定添加循环检查。但由于递归CTE的基于集合的方式运行，通常无法判断下一步是否会产生一个周期。
要执行“下一步”，整个“当前”集需要可用，但要生成当前集的每一行(包括循环列)，我们只需要获得“下一步”操作的结果。单行(例如CONNECT BY)不是问题，但是整个集合存在问题。
还没有研究过Oracle 11，但是sql Server通过隐藏它们后面的CONNECT BY来实现递归CTE，这需要放置许多限制(所有限制都有效地禁止所有基于集合的操作)。
另一方面，Postgresql的实现是真正基于集合的。
如前所述，MysqL根本没有实现CTE(它也没有实现HASH JOIN或MERGE JOIN，只有嵌套循环，因此不要太惊讶)。
具有讽刺意味的是，我今天收到了一封关于这个主题的信，我将在我的博客中介绍。
更新：
