sql - 通过先前的查询帮助连接

Question

我有以下数据

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

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

现在我需要一些 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

该查询只使用单次全表扫描，如果有索引也可以使用索引范围扫描。

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;

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