Oracle SQL Left 使用周期连接同一表的未知次数

Question

这是我上一个问题的后续问题：Oracle SQL Left 连接同一个表的次数未知

假设我有这个数据集

| old  | new   | 
|------|-------| 
| a    | b     | 
| b    | c     | 
| d    | e     | 
| e    | a     | <- Here is a cycle 
| ...  | ...   | 
| aa   | bb    | 
| bb   | ff    | 
| ...  | ...   | 
| 11   | 33    | 
| 33   | 523   | 
| 523  | 4444  | 
| 4444 | 33    | <- another cycle 

由于周期，Oracle 返回此错误："ORA-32044：执行递归 WITH 查询时检测到循环">

我想打破递归循环并检测导致循环的行

在下文中，可以用"<"打破循环

with numbers(val) as (
select 1 as val from dual
union all
select val + 1 from numbers
where val < 5 
)
select val from numbers

我尝试了以下方法：http://rextester.com/ITB3407

此处的代码相同：

with cte (old, new, lev) as
(
  select old, new, 1 as lev from mytable
  union all
  select m.old, cte.new, cte.lev + 1
  from mytable m
  join cte on cte.old = m.new
  where cte.lev < 6
)
select old, max(new) keep (dense_rank last order by lev) as new
from cte
group by old
order by old;

Answer 1

添加CYCLE子句。通过指定此子句，可以检测循环并停止递归

WITH cte (OLD, NEW, lev)
     AS (SELECT OLD, NEW, 1 AS lev FROM mytable
         UNION ALL
         SELECT m.old, cte.new, cte.lev + 1
           FROM mytable m JOIN cte ON cte.old = m.new
                                        )
  CYCLE OLD SET cycle TO 1 DEFAULT 0
  SELECT OLD, MAX (NEW) KEEP (DENSE_RANK LAST ORDER BY lev)
    FROM cte
GROUP BY OLD
ORDER BY OLD;

测试仪