SELECT distinct AID FroM (SELECT A.order_id AID,B.order_id BID FROM test_tbl A,test_tbl B WHERE A.bill_id=B.bill_id AND A.ROWID<>B.ROWID) START WITH AID='A' CONNECT BY NOCYCLE prior AID=BID 测试结果 AID A B C E
select distinct order_id from test_tbl t start with t.order_id='A' connect by nocycle (prior order_id!=order_id and prior bill_id=bill_id) or (prior bill_id!=bill_id and prior order_id= order_id)
可否考虑在数据库中建立函数来处理 未测试,参考 declare v_result varchar2(4000); i number; begin v_result:='A' loop select count(1) into i from test_tbl A where exists (SELECT 1 FROM test_tbl WHERE bill_id=A.bill_id and instr(','||v_result||',',','||order_id||',')>0) and instr(','||v_result||',',','||order_id||',')<=0 and rownum<2; if i=0 then exit; end if; select v_result||','||wmsys.wm_concat(distinct ) into v_result from test_tbl A where exists (SELECT 1 FROM test_tbl WHERE bill_id=A.bill_id and instr(','||v_result||',',','||order_id||',')>0) and instr(','||v_result||',',','||order_id||',')<=0; end loop; end;
(SELECT A.order_id AID,B.order_id BID
FROM test_tbl A,test_tbl B
WHERE A.bill_id=B.bill_id AND A.ROWID<>B.ROWID)
START WITH AID='A'
CONNECT BY NOCYCLE prior AID=BID
测试结果
AID
A
B
C
E
from test_tbl t
start with t.order_id='A'
connect by nocycle (prior order_id!=order_id and prior bill_id=bill_id)
or (prior bill_id!=bill_id and prior order_id= order_id)
AB改为左连接就可以了,这倒不是大问题
还是用大版的语句吧,测试了下,他的语句执行效率挺高的
未测试,参考
declare
v_result varchar2(4000);
i number;
begin
v_result:='A'
loop
select count(1) into i from test_tbl A
where exists (SELECT 1
FROM test_tbl
WHERE bill_id=A.bill_id and instr(','||v_result||',',','||order_id||',')>0)
and instr(','||v_result||',',','||order_id||',')<=0
and rownum<2;
if i=0 then exit; end if;
select v_result||','||wmsys.wm_concat(distinct ) into v_result from test_tbl A
where exists (SELECT 1
FROM test_tbl
WHERE bill_id=A.bill_id and instr(','||v_result||',',','||order_id||',')>0)
and instr(','||v_result||',',','||order_id||',')<=0;
end loop;
end;
但是那样的话需要语句动态执行