我希望我能够解释困扰我的问题。我有以下分层数据集（这只是34K记录的子集）
PARENT_ID CHILD_ID EXAM TUDA12802 TUDA12982 N TUDA12982 TUDA12984 J TUDA12984 TUDA999 J TUDA12982 TUDA12983 N TUDA12983 TUDA15322 J TUDA12983 TUDA15323 J
这是树的代表
TUDA12982 N - TUDA12984 J -- TUDA999 J - TUDA12983 N -- TUDA15322 J -- TUDA15323 J
我需要的是清单的所有记录，带有exam = N和潜在的extest =’J’记录，可以嵌套。
select * from test1 connect by prior child_id = parent_id start with child_id = 'TUDA12982' order siblings by child_id;
给我
PARENT_ID CHILD_ID EXAM TUDA12802 TUDA12982 N TUDA12982 TUDA12984 J TUDA12984 TUDA999 J TUDA12982 TUDA12983 N TUDA12983 TUDA15323 J TUDA12983 TUDA15322 J
但是我需要的是
TUDA12802 TUDA12982 N TUDA12982 TUDA12984 J TUDA12984 TUDA999 J
当我遇到EXAM =’N’记录时，需要停止运行。
我需要类似“停止于”子句的内容。
select * from test1 connect by prior child_id = parent_id start with child_id = 'TUDA12982' stop with exam = 'N' order siblings by child_id;
如何才能做到这一点？
罗伯特
您可以通过在connect by子句中添加“ exam =’J’”来做到这一点：
SQL> create table test1(parent_id,child_id,exam) 2 as 3 select 'TUDA12802', 'TUDA12982', 'N' from dual union all 4 select 'TUDA12982', 'TUDA12984', 'J' from dual union all 5 select 'TUDA12984', 'TUDA999', 'J' from dual union all 6 select 'TUDA12982', 'TUDA12983', 'N' from dual union all 7 select 'TUDA12983', 'TUDA15322', 'J' from dual union all 8 select 'TUDA12983', 'TUDA15323', 'J' from dual 9 / Tabel is aangemaakt. SQL> select parent_id 2 , child_id 3 , exam 4 , level 5 , lpad(' ',2*level) || sys_connect_by_path(parent_id||'-'||child_id,'/') scbp 6 from test1 7 start with exam = 'N' 8 connect by prior child_id = parent_id 9 and exam = 'J' 10 / PARENT_ID CHILD_ID E LEVEL SCBP --------- --------- - ------ ---------------------------------------------------------------------- TUDA12802 TUDA12982 N 1 /TUDA12802-TUDA12982 TUDA12982 TUDA12984 J 2 /TUDA12802-TUDA12982/TUDA12982-TUDA12984 TUDA12984 TUDA999 J 3 /TUDA12802-TUDA12982/TUDA12982-TUDA12984/TUDA12984-TUDA999 TUDA12982 TUDA12983 N 1 /TUDA12982-TUDA12983 TUDA12983 TUDA15322 J 2 /TUDA12982-TUDA12983/TUDA12983-TUDA15322 TUDA12983 TUDA15323 J 2 /TUDA12982-TUDA12983/TUDA12983-TUDA15323 6 rijen zijn geselecteerd.
