8-36 リーフノード,ブランチノード,ルートノードを判断

SQLパズル

BusyoTable
ID  OyaID  Name
--  -----  ----------
 1  null   織田信長
 2  1      堀久太郎
 3  1      羽柴筑前
 4  3      竹中半兵衛
 5  3      福島正則
 6  1      明智日向
 7  6      斎藤利三
 8  6      溝尾庄兵衛

prior ID = OyaID
を条件として階層を作成した時の
ノードが、リーフノード,ブランチノード,ルートノードのどれかを判断する。

出力結果
NodeType  ID  OyaID  Name
--------  --  -----  ----------
Root       1  null   織田信長
Leaf       2  1      堀久太郎
Branch     3  1      羽柴筑前
Leaf       4  3      竹中半兵衛
Leaf       5  3      福島正則
Branch     6  1      明智日向
Leaf       7  6      斎藤利三
Leaf       8  6      溝尾庄兵衛

SQLクックブックのレシピ13.5を参考にさせていただきました

データ作成スクリプト

create table BusyoTable(ID,OyaID,Name) as
select 1,null,'織田信長'   from dual union
select 2,1   ,'堀久太郎'   from dual union
select 3,1   ,'羽柴筑前'   from dual union
select 4,3   ,'竹中半兵衛' from dual union
select 5,3   ,'福島正則'   from dual union
select 6,1   ,'明智日向'   from dual union
select 7,6   ,'斎藤利三'   from dual union
select 8,6   ,'溝尾庄兵衛' from dual;

SQL

col NodeType for a10

select case when Level = 1 then 'Root'
            when Connect_by_IsLeaf=1 then 'Leaf'
       else 'Branch' end as NodeType,
ID,OyaID,Name
  from BusyoTable
Start With OyaID is null
connect by prior ID = OyaID;

解説

全てのノードは、根か枝か葉のいずれかであるので、
判断しやすい、根と葉を最初に判断しています。

根でかつ葉でもあるようなノード(孤立しているノード)は、
case式の結果が、'Root'となりますね。

---