以下のようなTREE構造をテーブルで保持している時「階層（レベル）」を知りたくなる状況があります。これを「自力」で求めるのは大変ですが、connect by prior句とLEVEL疑似列を使う事で簡単に導出できます。
社員名(社員番号)
空山(900) 空木(800) ←Level 1
│
┌────┴──┬─────┐
西海(100) 北海(400) 東海(600) ←Level 2
┌──┴─┐ │
西山(200) 大西(500) 東山(700) ←Level 3
│
西川(300) ←Level 4
階層表示
select max(lv) as "レベル",w.SNO,w.SNAME,w.JYOUSHI_SNO from ( select sno,sname,jyoushi_sno,LEVEL as lv from shain CONNECT BY PRIOR sno=jyoushi_sno ) w group by w.SNO,w.SNAME,w.JYOUSHI_SNO order by 1,2 ;
検証データ作成
create table shain(sno number,sname varchar2(10),jyoushi_sno number) ; insert all into shain(sno,sname,jyoushi_sno) values(900,'空山',null) into shain(sno,sname,jyoushi_sno) values(800,'空木',null) into shain(sno,sname,jyoushi_sno) values(100,'西海',900) into shain(sno,sname,jyoushi_sno) values(200,'西山',100) into shain(sno,sname,jyoushi_sno) values(300,'西川',200) into shain(sno,sname,jyoushi_sno) values(400,'北海',900) into shain(sno,sname,jyoushi_sno) values(500,'大西',100) into shain(sno,sname,jyoushi_sno) values(600,'東海',900) into shain(sno,sname,jyoushi_sno) values(700,'東山',600) select 1 from dual ;
動作検証
--
-- 検証用テーブルの作成
--
SQL> create table shain(sno number,sname varchar2(10),jyoushi_sno number)
2 ;
表が作成されました。
--
-- 最上位レベルのデータの上位関係カラム(今回の例ではjyoushi_sno)には
-- 紐付きの無い値をを設定する必要があります。一目見て「何処にも紐付かない事」
-- がわかる値を設定する事おすすめします。そういう意味ではNULLが最適のようにも
-- 感じます。注意点としては自身と同じ値を設定すると疑似列が正常に動作しません
--
-- を設定する事で表現できます。
--
SQL> insert all
2 into shain(sno,sname,jyoushi_sno) values(900,'空山',null)
3 into shain(sno,sname,jyoushi_sno) values(800,'空木',null)
4 into shain(sno,sname,jyoushi_sno) values(100,'西海',900)
5 into shain(sno,sname,jyoushi_sno) values(200,'西山',100)
6 into shain(sno,sname,jyoushi_sno) values(300,'西川',200)
7 into shain(sno,sname,jyoushi_sno) values(400,'北海',900)
8 into shain(sno,sname,jyoushi_sno) values(500,'大西',100)
9 into shain(sno,sname,jyoushi_sno) values(600,'東海',900)
10 into shain(sno,sname,jyoushi_sno) values(700,'東山',600)
11 select 1 from dual
12 ;
9行が作成されました。
SQL>
--
-- 投入データの確認
--
SQL> select sno,sname,jyoushi_sno from shain;
SNO SNAME JYOUSHI_SNO
---------- -------------------- -----------
900 空山
800 空木
100 西海 900
200 西山 100
300 西川 200
400 北海 900
500 大西 100
600 東海 900
700 東山 600
9行が選択されました。
SQL>
--
-- 階層の表示
-- 上下関係をあらわす項目を条件（sno=jyoushi_sno）で指定します
--
SQL> select max(lv) as "レベル",w.SNO,w.SNAME,w.JYOUSHI_SNO from
2 (
3 select sno,sname,jyoushi_sno,LEVEL as lv
4 from shain CONNECT BY PRIOR sno=jyoushi_sno
5 ) w
6 group by w.SNO,w.SNAME,w.JYOUSHI_SNO
7 order by 1,2
8 ;
レベル SNO SNAME JYOUSHI_SNO
---------- ---------- -------------------- -----------
1 800 空木
1 900 空山
2 100 西海 900
2 400 北海 900
2 600 東海 900
3 200 西山 100
3 500 大西 100
3 700 東山 600
4 300 西川 200
9行が選択されました。
SQL>
採番しながら集合（複数行）を返す
少し変わった使い方として以下のような事もできます。
select LEVEL - 1 AS seq from dual CONNECT BY LEVEL <= 10;
SQL> select LEVEL - 1 AS seq from dual CONNECT BY LEVEL <= 10;
SEQ
----------
0
1
2
3
4
5
6
7
8
9
10行が選択されました。
SQL>
