Question

甲状腺可使用其专有的CONNECT,自v2起就进行分级查询。在最近发表的第11g号声明中,他们增加了复位的分级系数,也称为重新适用条款。这是ANSI标准,如果我正确理解,该系统的其他供应商也执行了这一标准。

在比较连接与回馈时,我注意到在使用周期探测时所产生的结果有差异。成果之间的联系对我来说更有启发性,因此我很想知道,Oracle的实施工作是否含有ug,或者这是否是标准ANSI和预期行为。因此,我的问题是,如果你能够利用诸如MySQL、DB2、SQ和服务器等其他数据库,用问询来检查退学情况。为这些数据库提供当然条款的支持。

在这方面,它是如何在Oracle11.2.0.1.0上开展工作的。

SQL> select *
  2    from t
  3  /

        ID  PARENT_ID
---------- ----------
         1          2
         2          1

2 rows selected.

The query using CONNECT BY syntax:

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 syntax,它又一次返回:

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;

Answer 1

http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/pseudocolumns001.htm

<代码>CONNECT_BY_ISCYCLE p.eudocolumn Return 1 如果目前一行有一个儿童,也是其祖先

http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_10002.htm#BABFFJFD”rel=“nofollow noreferer”>:CYCLE:

如果其祖先之一对周期各栏的数值相同,则视为形成周期。

缩略语确实有一个儿童,也是其祖先,但id。尚未返回。

换言之,CONNECT_BY_ISCYCLE 检查<子女(尚未返回)和<编码> <>目前>浏览(已返回)。

<代码>CONNECT BY is row based, while recursive CTEs are set-based.

注:Oracles 文件CYCLE提到“存储器”。然而,一般而言,在一次复读的<编码>CTE中,没有“存储器”的概念。它是一种能够完全摆脱树木的固定作业。一般而言,固定部分和休养部分甚至可以使用不同的表格。

由于复读的<代码>CTEs> 是usually用于建造等级级树木的,Oracle决定添加一个周期核对表。但是,由于按既定方式操作<代码>CTE>,通常无法说明下一个步骤会产生周期性,因为没有明确界定“储存增长”周期条件,也无法界定。

为了执行“延伸”步骤,需要提供整个“当前”结构,但为了产生目前一套(包括周期一栏)的每一行,我们刚刚需要取得“扩展”行动的结果。

如果现行一套系统总是由单一行组成(如<条码>,按<>条码/代码>),则不成问题,但如果整个组别的宽恕行动界定,则这是一个问题。

D. Didn took into Oracle 11, but SQ服务器/code> Implements recursive CTEs by onlyidden acode>CONNECT BY, 这就需要设置许多限制(所有这些限制都有效地禁止了所有固定业务)。

<编码> 另一方面,执行《邮政则真正有条不紊:你可以在休补部分内从事任何业务。它没有任何手段探测周期,但因为周期首先没有界定。

如前所述,<代码>MySQL。没有执行<条码>。 HASH JOIN s or MERGE JOINs, only the nested loops, so t not be惊讶.

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

<>Update:

<代码>CTE s in SQL服务器 不超过CONNECT BY,其伪装。在我对令人震惊的细节表示歉意时,见这一条:

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

Answer 2

邮电局支持采用固定等级的查询,但无自动周期检测。这意味着,你需要写自己的文字,退还的行文数目取决于你如何在 que中说明加入条件。

两个例子都使用一个阵列(所谓的所有物体)探测 lo:

WITH recursive tr (id, parent_id, all_ids, cycle) AS (
    SELECT id, parent_id, ARRAY[id], false
    FROM t
    WHERE id = 1
    UNION ALL
    SELECT t.id, t.parent_id, all_ids || t.id, t.id = ANY(all_ids)
    FROM t
    JOIN tr ON t.parent_id = tr.id AND NOT cycle)
SELECT id, parent_id, cycle
FROM tr;

 id | parent_id | cycle
----+-----------+-------
  1 |         2 | f
  2 |         1 | f
  1 |         2 | t


WITH recursive tr (id, parent_id, all_ids, cycle) AS (
    SELECT id, parent_id, ARRAY[id], false
    FROM t
    WHERE id = 1
    UNION ALL
    SELECT t.id, t.parent_id, all_ids || t.id, (EXISTS(SELECT 1 FROM t AS x WHERE x.id = t.parent_id))
    FROM t
    JOIN tr ON t.parent_id = tr.id
    WHERE NOT t.id = ANY(all_ids))
SELECT id, parent_id, cycle
FROM tr;

 id | parent_id | cycle
----+-----------+-------
  1 |         2 | f
  2 |         1 | t

Answer 3

AFAIK:

MySQL doesn t support recursive CTE s
SQL Sever does not support cycle detection in recursive CTE s

Answer 4

连接的结果不一定总是直观。

下面的询问表明,从<代码>id = 3开始,对图片图表的检测周期有不同的方法。

create table graph (id, id_parent) as
(select 2, 1 from dual
union all select 3, 1 from dual
union all select 4, 3 from dual
union all select 5, 4 from dual
union all select 3, 5 from dual)

SQL> select level lvl, graph.*, connect_by_iscycle cycle
  2    from graph
  3   start with id = 3
  4  connect by nocycle prior id = id_parent;

       LVL         ID  ID_PARENT      CYCLE
---------- ---------- ---------- ----------
         1          3          1          0
         2          4          3          0
         3          5          4          1
         1          3          5          0
         2          4          3          0
         3          5          4          1

6 rows selected.

SQL> select level lvl, graph.*, connect_by_iscycle cycle
  2    from graph
  3   start with id = 3
  4  connect by nocycle prior id = id_parent
  5         以及 prior id_parent is not null;

       LVL         ID  ID_PARENT      CYCLE
---------- ---------- ---------- ----------
         1          3          1          0
         2          4          3          0
         3          5          4          0
         4          3          5          1
         1          3          5          0
         2          4          3          0
         3          5          4          1

7 rows selected.

SQL> with t(id, id_parent) as
  2   (select *
  3      from graph
  4     where id = 3
  5    union all
  6    select g.id, g.id_parent
  7      from t
  8      join graph g
  9        on t.id = g.id_parent)
 10  search depth first by id set ord
 11  cycle id set cycle to 1 default 0
 12  select * from t;

        ID  ID_PARENT        ORD C
---------- ---------- ---------- -
         3          1          1 0
         4          3          2 0
         5          4          3 0
         3          5          4 1
         3          5          5 0
         4          3          6 0
         5          4          7 0
         3          5          8 1

8 rows selected.

Node with id = 3, 有两个父母,即Oracle 写法,在这个例子中有两个周期。

(1, 3) -> (3, 4) -> (4, 5) -> (5, 3)

以及

(5, 3) -> (3, 4) -> (4, 5)

Edge (5, 3) is missing from the result of the first query 以及 first cycle. At the same time edge (5, 3) appears in the result for the third query 以及 second cycle twice.

为什么如此? 您可在Quesnoi提供的答复中核实对周期探测逻辑的描述。英语意味着

(1) connect by detects a cycle if child ID for current row is part of IDs visited so far

(2) rec with detects a cycle if ID for current row is part of IDs visited so far

Result of the second query looks the most natural although there is additional predicate 以及 prior id_parent is not null. In this case

(3) it detects a cycle if ID for current row is part of parent IDs visited so far

所有这些条件都在以下电线1、cnt2、cnt3栏中实施。

SQL> with t(id, id_parent, path_id, path_id_parent, cnt1, cnt2, cnt3) as
  2   (select g.*,
  3           cast( ->  || g.id as varchar2(4000)),
  4           cast( ->  || g.id_parent as varchar2(4000)),
  5           0,
  6           0,
  7           0
  8      from graph g
  9     where id = 3
 10    union all
 11    select g.id,
 12           g.id_parent,
 13           t.path_id ||  ->  || g.id,
 14           t.path_id_parent ||  ->  || g.id_parent,
 15           regexp_count(t.path_id ||  -> ,  ->  ||
 16            (select id from graph c where c.id_parent = g.id) ||  -> ),
 17           regexp_count(t.path_id ||  -> ,  ->  || g.id ||  -> ),
 18           regexp_count(t.path_id_parent ||  -> ,  ->  || g.id ||  -> )
 19      from t
 20      join graph g
 21        on t.id = g.id_parent
 22    -- 以及 t.cnt1 = 0
 23    -- 以及 t.cnt2 = 0
 24    -- 以及 t.cnt3 = 0
 25    )
 26  search depth first by id set ord
 27  cycle id set cycle to 1 default 0
 28  select * from t;

        ID  ID_PARENT PATH_ID         PATH_ID_PARENT  CNT1 CNT2 CNT3        ORD C
---------- ---------- --------------- --------------- ---- ---- ---- ---------- -
         3          1 ->3             ->1                0    0    0          1 0
         4          3 ->3->4          ->1->3             0    0    0          2 0
         5          4 ->3->4->5       ->1->3->4          1    0    0          3 0
         3          5 ->3->4->5->3    ->1->3->4->5       1    1    1          4 1
         3          5 ->3             ->5                0    0    0          5 0
         4          3 ->3->4          ->5->3             0    0    0          6 0
         5          4 ->3->4->5       ->5->3->4          1    0    1          7 0
         3          5 ->3->4->5->3    ->5->3->4->5       1    1    1          8 1

8 rows selected.

If you uncomment filter by cnt1/cnt2/cnt3 以及 remove "cycle id set cycle to 1 default 0" then query will return result as corresponding query above. In other words you can avoid cycle clause 以及 implement whatever cycle detection logic you find more intuitive.

Additional details about traversing hierarchies 以及 cycle detection can be found in the book Oracle SQL Revealed.

Answer 5

缩略语

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near with tr (id, parent_id) as (select id, parent_id from t where id = 1 union all s at line 1.

Answer 6

WITH RECURSIVE s (master, slave, all_ids, cycle) AS
(
    SELECT master, slave, ARRAY[master], false FROM binding WHERE master=3477

    UNION ALL

    SELECT d.master, d.slave, all_ids || d.master, d.slave = ANY(all_ids)
    FROM
        binding AS d
    JOIN
        s
    ON (d.master = s.slave)
    WHERE NOT d.master = ANY(all_ids)
)
SELECT *
FROM s;

我认为,这一条件较好:d.slave = ANY(all_ids)。

Answer 7

回答的问题是,如果你能够利用诸如MySQL、DB2、服务器和其他数据库对休庭进行查询。”

MariaDB 10.5.2和新的支助周期探测:

https://mariadb.com/kb/en/with/>rel=“nofollow noreferer”>

《消费物价指数》条款使幼儿教育周期得以检测,避免了过度的或无限的休息,因此,MariaDB支持一种放松的、非标准制图。

WITH RECURSIVE ... ( ... ) CYCLE <cycle column list> RESTRICT

例:

CREATE TABLE t(id INT, parent_id INT); INSERT INTO t(id, parent_id) VALUES (1, NULL),(2,1),(3,2),(1,3); WITH RECURSIVE cte AS ( SELECT id, parent_id, 0 lvl FROM t WHERE parent_id IS NULL UNION ALL SELECT t.id, t.parent_id, lvl + 1 AS lvl FROM cte c1 JOIN t ON c1.id = t.parent_id ) CYCLE id, parent_id RESTRICT SELECT * FROM cte ORDER BY lvl;

<https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=401ecaf984654be1f7c6fa588fbda843”rel=“nofollow noreferer”>db<>fiddle demo

友情链接