代码之家 › 专栏 › 技术社区 › petruson Kaushik Nayak

Oracle SQL左连接同一个表的未知次数和周期

cycle recursion oracle sql

petruson Kaushik Nayak · 技术社区 · 6 年前

这是我上一个问题的后续问题: Oracle SQL Left join same table unknown amount of times

假设我有这个数据集

| 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 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;

1 回复 | 直到 6 年前

petruson Kaushik Nayak 6 年前

添加 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;

REXTESTER