sql – 使用递归子查询因子进行循环检测

前端之家收集整理的这篇文章主要介绍了sql – 使用递归子查询因子进行循环检测前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
自从v2以来,Oracle sql可以使用其专有的CONNECT BY语法进行分层查询。在他们最新的11g版本2中,他们添加了递归子查询因子,也称为递归子句。这是ANSI标准,如果我理解正确,这个也已由其他RDBMS供应商实现。

将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_ISCYCLE pseudocolumn returns 1 if 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,只有嵌套循环,因此不要太惊讶)。

具有讽刺意味的是,我今天收到了一封关于这个主题的信,我将在我的博客中介绍。

更新:

sql Server中的递归CTE只是伪装成CONNECT BY。有关令人震惊的详细信息,请参阅我的博客中的这

> SQL Server: are the recursive CTE’s really set-based?

原文链接:https://www.f2er.com/mssql/84425.html

猜你在找的MsSQL相关文章