通过事先查询帮助建立连接

Question

我有以下数据

Order_ID  Pallet_ID
O1        P1
O2        P1
O2        P2
O3        P2
O3        P3
O4        P4

，其中订单可以位于多个托盘上，并且一个托盘上可以有多个订单。我需要选择组成一个组的订单组，因为它们都共享同一组托盘。在上面的测试数据中，有两个这样的组，{O1，O2，O3}和{O4}，因为O1，O2和O3与该组的另一成员至少有一个共同的托盘。

现在我需要一些 SQL 来做到这一点。我尝试过（其中 greg_test 包含上面的数据）

select distinct order_id
from greg_test
start with order_id = :order_id
connect by pallet_id = prior pallet_id

但这给了我一个循环引用错误（ORA-01436 CONNECT BY 用户数据中的循环）。添加 nocycle 没有给出正确的集合。

Answer 1

-- Z lists all order pairs that share a pallet, and also pairs each order with itself
WITH pairs AS (
    -- all pairs of orders on the same pallet
    SELECT DISTINCT a.order_id a, b.order_id b FROM greg_test a, greg_test b 
    WHERE a.pallet_id = b.pallet_id AND a.order_id != b.order_id
  UNION ALL 
    -- pair all orders with themselves
    SELECT DISTINCT order_id a, order_id b FROM greg_test
)
-- Now connect all the pairs
SELECT DISTINCT a FROM pairs 
CONNECT BY NOCYCLE PRIOR a = b 
START WITH a = :order_id

也许有更有效的解决方案。

Answer 2

该查询仅使用单个全表扫描，或者如果有索引则可以使用索引范围扫描。

select distinct order_id
from greg_test
start with order_id = :order_id
connect by nocycle pallet_id = prior pallet_id or order_id = prior order_id;

如果您使用的是 11gR2，这将比上面的通过查询连接运行得更快一些，尽管语法在我看来更奇怪。

with orders(order_id, pallet_id) as
(
    select order_id, pallet_id
    from greg_test
    where order_id = :order_id
    union all
    select greg_test.order_id, greg_test.pallet_id
    from greg_test
    inner join orders
        on greg_test.pallet_id = orders.pallet_id
            or greg_test.pallet_id = orders.pallet_id
) cycle order_id, pallet_id set is_cycle to 'Y' default 'N'
select distinct order_id from orders;

如果您有大量数据，您将需要彻底测试您使用的任何解决方案。分层查询通常会出现严重的性能问题。