10-166 根からたどれないデータを取得

SQLパズル

TestTable
Code  ID  NextID
----  --  ------
1111   1       2
1111   2       3
1111   3       4
1111   4       8
1111   5       6  ←出力対象
1111   6       7  ←出力対象
1111   8       9
2222   1       2
2222   2       3
2222   4       5  ←出力対象
2222   5       6  ←出力対象
2222   6       7  ←出力対象
2222   3       8
2222   8       9

Codeごとに、
IDが1のデータからNextIDをたどっていっても、到達しないデータを出力する。

データ作成スクリプト

create table TestTable as
select 1 Code,1 as ID,2 as NextID from dual
union select 1,2,3 from dual
union select 1,3,4 from dual
union select 1,4,8 from dual
union select 1,5,6 from dual
union select 1,6,7 from dual
union select 1,8,9 from dual
union select 2,1,2 from dual
union select 2,2,3 from dual
union select 2,4,5 from dual
union select 2,5,6 from dual
union select 2,6,7 from dual
union select 2,3,8 from dual
union select 2,8,9 from dual;

SQL

--■■■たどれる部分との差集合を求める方法■■■
select Code,ID,NextID
  from TestTable
minus
select Code,ID,NextID
  from TestTable
 start with ID = 1
connect by prior Code = Code
       and prior NextID = ID;

--■■■たどれない部分を階層問い合わせで求める方法■■■
select Code,ID,NextID
  from TestTable a
Start With not exists(select 1 from TestTable b
                       where b.Code = a.Code
                         and b.NextID = a.ID)
           and ID != 1
connect by prior Code = Code
       and prior NextID = ID;

解説

Start With句と
connect by句では、
論理演算を行うことができるのです。

---