时间戳

首页 > 解决方案 > 为了避免在递归 CTE 中使用 2 个 Oracle / SQL 表进行循环引用

sql - 为了避免在递归 CTE 中使用 2 个 Oracle / SQL 表进行循环引用

问题描述

有 2 个表 Department 和 subdepartment 有共同的 id。我正在尝试递归地直接和间接地获取所有向 AB 报告的 id。BC 向 AB 报告，因此 4,5,6 间接向 AB 报告，同样获取到最后一个 id。

Id 9 对 BC 存在循环引用。我想避免循环引用。我在 Oracle 11g 和 SQL server 2012 部门尝试

Name     id
AB          1
AB          2
AB          3
BC          4
BC          5
BC          6
CD          7
CD          8
EF          9
EF         10
EF         11

子部门

ID      Reporting
1
2
3         BC
4
5         CD
6
7
8         EF
9         BC
10
11

询问：

With reportinghierarchy (Name, Id, Reporting, lvl) As
    (   
    --Anchor
    Select A.name,
           A.id,
           reporting,
           0 
    from department A, 
         subdepartment B
    where A.id=B.id 
    and A.name='AB'        
    Union All        
    --Recursive member
    Select C.name,
           C.id,
           D.reporting, 
           lvl+1 
    from department C, 
         subdepartment D, 
         reportinghierarchy R
    Where C.name != 'AB' 
    and C.Id =D.id 
    and C.Name = R.reporting
    And R.Reporting is not null
    )
Select * from reportinghierarchy;

收到错误为“ORA-32044：执行递归 WITH 时检测到循环”

标签： sqloracle

解决方案

我认为您只是在寻找以下cycle条款：

with reportinghierarchy (name, id, reporting, lvl) as
(
  select a.name, a.id, reporting, 0
  from department a
  join subdepartment b
  on b.id = a.id
  where a.name='AB'
  union all
  select c.name, c.id, d.reporting, lvl+1
  from reportinghierarchy r
  join department c
  on c.name = r.reporting
  join subdepartment d
  on d.id = c.id
  where c.name != 'AB'
  and r.reporting is not null
)
cycle reporting set is_cycle to 1 default 0
select *
from reportinghierarchy;

您的示例日期给出：

NAME         ID REPORTING        LVL I
---- ---------- --------- ---------- -
AB            1                    0 0
AB            2                    0 0
AB            3 BC                 0 0
BC            6                    1 0
BC            5 CD                 1 0
BC            4                    1 0
CD            8 EF                 2 0
CD            7                    2 0
EF           11                    3 0
EF           10                    3 0
EF            9 BC                 3 1

11 rows selected.

您可以过滤is_cycle伪列，和/或通过列出您想要查看的列而不是使用*.

在文档中阅读有关递归子查询分解如何工作的更多信息。

推荐阅读