Oracle -按优先级连接

slmsl1lt 于 2023-05-28 发布在 Oracle

关注(0)|答案(2)|浏览(139)

我知道我必须在这个查询中使用CONNECT BY PRIOR，但我不确定如何实现它。
我们有客户谁购买每月订阅，那些得到自动更新每个月。我们有一个日志表，可以显示您当前的订单ID和您以前的订单ID。因此，表记录可能看起来像这样：

CUSTOMER ID: 1     ORDER ID: 123   PREV_ORDER_ID:       STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 456   PREV_ORDER_ID: 123   STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 789   PREV_ORDER_ID: 456   STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 888   PREV_ORDER_ID: 789   STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 999   PREV_ORDER_ID: 888   STATUS: Active

我期待着计算有多少客户有至少13个月的连续订阅，与最近的订阅没有差距将有一个“活跃”的状态。如果订阅中断，PREV_ORDER_ID将为NULL。
我希望在查询中做到这一点，而不必为它编写匿名块。
非常感谢！

oracle

来源：https://stackoverflow.com/questions/67644851/oracle-connect-by-prior

2条答案

按热度按时间

gorkyyrv1#

您可以这样做（使用实际的表名和列名，而不是应该删除的查询中的with子句）。请注意，分层递归从末尾（从'Active'状态）开始并向后进行;在我查询中，我在级别4停止它，因为我不想写入足够的行来到达级别13。当然，您必须在where子句中将4替换为13。

with
  test_data (customer_id, order_id, prev_order_id, status) as (
    select 1, 123, null, 'Complete' from dual union all
    select 1, 456,  123, 'Complete' from dual union all
    select 1, 789,  456, 'Complete' from dual union all
    select 1, 888,  789, 'Complete' from dual union all
    select 1, 999,  888, 'Active'   from dual union all
    select 2, 100, null, 'Complete' from dual union all
    select 2, 200,  100, 'Active'   from dual union all
    select 5, 105, null, 'Complete' from dual union all
    select 5, 106,  105, 'Complete' from dual union all
    select 5, 205, null, 'Complete' from dual union all
    select 5, 206,  205, 'Active'   from dual
  )
select  customer_id
from    test_data
where   level = 4
start   with status = 'Active'
connect by customer_id = prior customer_id and order_id = prior prev_order_id
;

CUSTOMER_ID
-----------
          1

赞(0）回复(0）举报 2023-05-28

mum43rcc2#

需要更多的数据和测试。
也许会对你有帮助

CREATE TABLE CUSTOMER_LOG
(
CUSTOMER_ID number(5),
ORDER_ID number(5),
PREV_ORDER_ID number(5),
STATUS VARCHAR(50)
);

INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,123,NULL, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,456,123, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,789,456, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,888,789, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,999,888, 'Active');

Select
l.*,
(
select count(*)
from CUSTOMER_LOG s
where s.customer_id=1
start with s.ORDER_ID=l.ORDER_ID
connect by s.ORDER_ID= prior s.PREV_ORDER_ID   
) QTDE
from CUSTOMER_LOG l
where l.status='Active'

赞(0）回复(0）举报 2023-05-28

我来回答

Oracle -按优先级连接

2条答案

相关问题

热门标签

最新问答