10-138 階層の1番目と2番目を表示

SQLパズル

武将テーブル
親ID  ID    名称        兵力
----  ----  --------   ------
null     1  東軍         null
null     2  西軍         null
   1    11  徳川家康    10000
   1    12  福島正則     7500
   1    13  黒田長政     3500
   1    14  細川忠興     4000
   1    15  細川幽斎      500
   2    21  石田三成    10000
   2    22  毛利輝元     8500
   2    23  島津義弘     1000
   2    24  小西行長     7500
  11    111 本多忠勝      500
  11    112 井伊直正     3000
  12    121 可児才蔵     1500
  21    211 島左近       2000
  21    212 蒲生郷舎     1500
  23    231 島津豊久      500
 111   1111 本多忠政      100

階層問い合わせを使って、
以下の出力結果を作成します

出力結果
所属武将   所属   名称      兵力
--------  ----   --------  ------
徳川家康   東軍   徳川家康   10000
徳川家康   東軍   本多忠勝     500
徳川家康   東軍   本多忠政     100
徳川家康   東軍   井伊直正    3000
福島正則   東軍   福島正則    7500
福島正則   東軍   可児才蔵    1500
黒田長政   東軍   黒田長政    3500
細川忠興   東軍   細川忠興    4000
細川幽斎   東軍   細川幽斎     500
石田三成   西軍   石田三成   10000
石田三成   西軍   島左近      2000
石田三成   西軍   蒲生郷舎    1500
毛利輝元   西軍   毛利輝元    8500
島津義弘   西軍   島津義弘    1500
島津義弘   西軍   島津豊久     500
小西行長   西軍   小西行長    7500

データ作成スクリプト

create table 武将 as
select to_number(null) as 親ID,1 as ID,'東軍' as 名称,to_number(null) as 兵力 from dual
union all select null,   2,'西軍',null from dual
union all select    1,  11,'徳川家康',10000 from dual
union all select    1,  12,'福島正則', 7500 from dual
union all select    1,  13,'黒田長政', 3500 from dual
union all select    1,  14,'細川忠興', 4000 from dual
union all select    1,  15,'細川幽斎',  500 from dual
union all select    2,  21,'石田三成',10000 from dual
union all select    2,  22,'毛利輝元', 8500 from dual
union all select    2,  23,'島津義弘', 1500 from dual
union all select    2,  24,'小西行長', 7500 from dual
union all select   11, 111,'本多忠勝',  500 from dual
union all select   11, 112,'井伊直正', 3000 from dual
union all select   12, 121,'可児才蔵', 1500 from dual
union all select   21, 211,'島左近'  , 2000 from dual
union all select   21, 212,'蒲生郷舎', 1500 from dual
union all select   23, 231,'島津豊久',  500 from dual
union all select  111,1111,'本多忠政',  100 from dual;

SQL

select Last_Value(指揮武将 ignore nulls) over(order by 深さ優先での探索順) as 所属武将,
所属,名称,兵力
from (select 所属,名称,兵力,指揮武将,LV,RowNum as 深さ優先での探索順
        from (select connect_by_root 名称 as 所属,名称,兵力,
              decode(Level,2,名称) as 指揮武将,
              Level as LV
                from 武将
              Start With 親ID is null
              connect by prior ID = 親ID
              order siblings by 親ID,ID))
where LV >=2
order by 深さ優先での探索順;

解説

マニュアルによると、
階層問い合わせは、深さ優先探索で行を返すらしいですが、
order byがないと、順序は保証されないという記述もあるので、
order siblings byでソートしたほうが無難だろうと思います

---

マニュアル(英語)
マニュアル

---