7-69 (親子条件が複数で)階層問い合わせ

SQLパズル

階層テーブル
Code  SubCode  名前      親Code  親SubCode
----  -------  --------  ------  ---------
   1        1  織田信秀       1          1
   1        2  武田信虎       1          2
   1        3  真田幸隆       1          3
   2        1  織田信長       1          1
   2        2  織田市         1          1
   2        3  豊臣淀         2          2
   2        4  豊臣秀頼       2          3
   2        5  徳川江         2          2
   2        6  徳川家光       2          5
   2        7  織田信忠       2          1
   3        1  武田信玄       1          2
   3        2  武田勝頼       3          1
   4        1  真田信綱       1          3
   4        2  真田昌輝       1          3
   4        3  真田昌幸       1          3
   4        4  真田信之       4          3
   4        5  真田幸村       4          3
   4        6  真田大助       4          5

階層テーブルのから、
Code=上位Code、かつ、SubCode=上位SubCodeのレコードをルートとして、
階層情報を出力する。

出力結果
名前              系譜
----------------  ------------------------------------
■織田信秀        織田信秀
■■織田信長      織田信秀-織田信長
■■■織田信忠    織田信秀-織田信長-織田信忠
■■織田市        織田信秀-織田市
■■■豊臣淀      織田信秀-織田市  -豊臣淀
■■■■豊臣秀頼  織田信秀-織田市  -豊臣淀  -豊臣秀頼
■■■徳川江      織田信秀-織田市  -徳川江
■■■■徳川家光  織田信秀-織田市  -徳川江  -徳川家光
■武田信虎        武田信虎
■■武田信玄      武田信虎-武田信玄
■■■武田勝頼    武田信虎-武田信玄-武田勝頼
■真田幸隆        真田幸隆
■■真田信綱      真田幸隆-真田信綱
■■真田昌輝      真田幸隆-真田昌輝
■■真田昌幸      真田幸隆-真田昌幸
■■■真田信之    真田幸隆-真田昌幸-真田信之
■■■真田幸村    真田幸隆-真田昌幸-真田幸村
■■■■真田大助  真田幸隆-真田昌幸-真田幸村-真田大助

データ作成スクリプト

create table 階層テーブル(
Code      number(1),
SubCode   number(1),
名前      char(8),
親Code    number(1),
親SubCode number(1));

insert into 階層テーブル values(1,1,'織田信秀',1,1);
insert into 階層テーブル values(1,2,'武田信虎',1,2);
insert into 階層テーブル values(1,3,'真田幸隆',1,3);
insert into 階層テーブル values(2,1,'織田信長',1,1);
insert into 階層テーブル values(2,2,'織田市'  ,1,1);
insert into 階層テーブル values(2,3,'豊臣淀'  ,2,2);
insert into 階層テーブル values(2,4,'豊臣秀頼',2,3);
insert into 階層テーブル values(2,5,'徳川江'  ,2,2);
insert into 階層テーブル values(2,6,'徳川家光',2,5);
insert into 階層テーブル values(2,7,'織田信忠',2,1);
insert into 階層テーブル values(3,1,'武田信玄',1,2);
insert into 階層テーブル values(3,2,'武田勝頼',3,1);
insert into 階層テーブル values(4,1,'真田信綱',1,3);
insert into 階層テーブル values(4,2,'真田昌輝',1,3);
insert into 階層テーブル values(4,3,'真田昌幸',1,3);
insert into 階層テーブル values(4,4,'真田信之',4,3);
insert into 階層テーブル values(4,5,'真田幸村',4,3);
insert into 階層テーブル values(4,6,'真田大助',4,5);
commit;

SQL

col 名前 for a20
col 系譜 for a40

select LPad('■',Level*2,'■') || 名前 as 名前,
substr(sys_connect_by_path(名前,'-'),2) as 系譜
from 階層テーブル
start with Code = 親Code and SubCode = 親SubCode
connect by Prior Code = 親Code
       and Prior SubCode = 親SubCode
       and (Code != 親Code or SubCode != 親SubCode);

解説

start with句の、(Code = 親Code and SubCode = 親SubCode)
の否定を、ドモルガンの法則を使って変形した条件を、
connect by句で、
(Code != 親Code or SubCode != 親SubCode)といった形で使用して、
無限ループを防いでます。

---