使用CONNECT BY PRIOR进行分层查询-Oracle SQL

我认为您正在寻找类似的东西：

with rec(root_order, order_number, requirement_id, replenishment_id, replenishment_type,
    replenishment_detail, replenishment_date)
as (
  -- anchor member
  select
    orders.order_number as root_order,
    orders.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from orders
  join requirements on orders.order_number = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
  union all
  -- recursive member
  select rec.root_order,
    requirements.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from rec
  join requirements on rec.replenishment_detail = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
)
select *
from rec
order by root_order, order_number, requirement_id;

锚成员本质上是您的原始查询，除了它添加，root_order并且我使第一个联接成为内部查询以降低噪音（原始的87行中很多order_number都只有null）。

然后，递归成员加入rec.replenishment_detail（子订单号）requirements.order_number以遍历层次结构。不需要再次引用实际的订单表（除非您实际上想要从中获取其他字段，在这种情况下，将其包含在内很简单）。

使用您的样本数据可以产生65行输出，包括：

ROOT_ORDER ORDER_NUMBER REQUIR REPLE REPLEN REPLENISHMENT_DETAIL REPLENISHM
---------- ------------ ------ ----- ------ -------------------- ----------
...
       300          130 AA-171 RO532 Make                    727 2018-05-17
       300          130 AA-171 RO92  Bought                  493 2018-04-01
       300          130 AA-785 RO573 Make                    616 2018-04-01
       300          130 AA-785 RO561 Make                    453 2018-05-13
       300          130 AA-785 RO775 Make                    974 2018-08-07
       300          130 AA-785 RO139 Make                    698 2018-07-16
       300          130 AA-785 RO252 Make                    190 2018-08-02
       300          300 AA-300 RO601 Bought                  963 2018-07-15
       300          300 AA-300 RO111 Make                    251 2018-10-23
       300          300 AA-300 RO435 Make                    837 2018-03-04
       300          300 AA-300 RO608 Make                    850 2018-04-27
       300          300 AA-468 RO96  Bought                  279 2018-06-11
       300          300 AA-468 RO120 Make                    333 2018-07-28
       300          300 AA-516 RO754 Bought                  874 2018-06-09
       300          300 AA-516 RO245 Bought                  130 2018-02-06
       300          300 AA-516 RO734 Make                    415 2018-05-05
       300          300 AA-744 RO169 Make                    804 2018-02-02
       300          300 AA-744 RO576 Make                    452 2018-06-09
       300          300 AA-744 RO592 Bought                  967 2018-01-16
       300          300 AA-744 RO104 Make                    232 2019-01-30
       300          493 AA-400 RO4   Bought                  591 2018-04-17
       300          493 AA-401                                             
...

db <> fiddle根据您的原始版本。

请注意，尽管它还独立包含“子”订单：

...
       130          130 AA-171 RO92  Bought                  493 2018-04-01
       130          130 AA-171 RO532 Make                    727 2018-05-17
       130          130 AA-785 RO775 Make                    974 2018-08-07
       130          130 AA-785 RO561 Make                    453 2018-05-13
       130          130 AA-785 RO252 Make                    190 2018-08-02
       130          130 AA-785 RO573 Make                    616 2018-04-01
       130          130 AA-785 RO139 Make                    698 2018-07-16
       130          493 AA-400 RO4   Bought                  591 2018-04-17
       130          493 AA-401                                             
...
       493          493 AA-400 RO4   Bought                  591 2018-04-17
       493          493 AA-401                                             
...

等等。您可以从特定的目标订单开始（例如，锚点成员拥有where orders.order_number = 300），但是尚不清楚这是否是您想要的。如果不是这样，并且您也不想自己查看较低的订单，那么您需要一种方法来识别顶级订单。一种方法是replenishment_detail通过添加not exists(...)过滤器来排除显示为任何值的所有订单：

with rec(root_order, order_number, requirement_id, replenishment_id, replenishment_type,
    replenishment_detail, replenishment_date)
as (
  -- anchor member
  select
    orders.order_number as root_order,
    orders.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from orders
  join requirements on orders.order_number = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
  where not exists (
    select *
    from replenishments
    where replenishment_detail = orders.order_number
  )
  union all
  -- recursive member
  select rec.root_order,
    requirements.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date
  from rec
  join requirements on rec.replenishment_detail = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
)
select *
from rec
order by root_order, order_number, requirement_id;

现在只获得54行，并且不包括130/493 / etc。上面的根顺序行。

db <>小提琴

由于您实际上询问的是分层查询而不是递归查询，因此可以执行以下操作：

with cte (order_number, requirement_id, replenishment_id, replenishment_type,
    replenishment_detail, replenishment_date, is_root_order)
as (
  select
    orders.order_number,
    requirements.requirement_id,
    replenishments.replenishment_id,
    replenishments.replenishment_type,
    replenishments.replenishment_detail,
    replenishments.replenishment_date,
    case when exists (
      select *
      from replenishments
      where replenishment_detail = orders.order_number
    ) then 'N' else 'Y' end
  from orders
  join requirements on orders.order_number = requirements.order_number
  left join lookup on requirements.requirement_id = lookup.requirement_id
  left join replenishments on lookup.replenishment_id = replenishments.replenishment_id
)
select connect_by_root(order_number) as root_order,
  order_number, requirement_id, replenishment_id, replenishment_type,
  replenishment_detail, replenishment_date
from cte
start with is_root_order = 'Y'
connect by order_number = prior replenishment_detail;

CTE仍然是您的原始查询，具有case表达式和exists子句，用于像以前一样确定每个订单是否为“根”订单，但现在作为标志而不是过滤器。然后，在其starts with子句中使用该标志，分层查询就相当简单。

另一个db <> fiddle。

（我刚刚意识到这几乎是@StewAshton所说的；我的CTE本质上是他的“替换您的联接”步骤。唯一的真正不同是他将标志计算直接移到了starts with子句中，实际上效率更高，因为它不必replenishments再次打桌子...）

我通常更喜欢递归CTE方法，但层次结构方法在此仅出于其简洁性就很有吸引力。但是，您可能希望将两种方法的性能与实际数据进行比较。