SQLクリニック（6）　Page 1/2

SQLの抽出結果を階層構造で表示するテクニック

株式会社インサイトテクノロジー
林 優
2005/7/28
本連載はSQLの応用力を身に付けたいエンジニア向けに、さまざまなテクニックを紹介する。SQLの基本構文は平易なものだが、実務で活用するには教科書的な記述を理解するだけでは不十分だ。本連載は、著名なメールマガジン「おら！ オラ！ Oracle - どっぷり検証生活」を発行するインサイトテクノロジーのコンサルタントを執筆陣に迎え、SQLのセンス向上に役立つ大技小技を紹介していく。（編集局）
主な内容
--Page 1--
階層問い合わせの基本
--Page 2--
表の結合で階層問い合わせを使う
実行計画を階層問い合わせしてみる

　これまではSQL文の視認性を向上させるテクニックをご紹介してきましたが、今回は抽出結果の視認性を向上させる階層問い合わせをご紹介します。

■階層問い合わせの基本

　階層問い合わせの一番よい例は実行計画だと思いますが、その前にまず階層問い合わせの基本からおさらいしましょう。

SQL> select empno,ename,job,mgr,sal,comm from emp;

     EMPNO ENAME      JOB              MGR        SAL       COMM
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902        800
      7499 ALLEN      SALESMAN        7698       1600        300
      7521 WARD       SALESMAN        7698       1250        500
      7566 JONES      MANAGER         7839       2975
      7654 MARTIN     SALESMAN        7698       1250       1400
      7698 BLAKE      MANAGER         7839       2850
      7782 CLARK      MANAGER         7839       2450
      7839 KING       PRESIDENT                  5000
      7844 TURNER     SALESMAN        7698       1500          0
      7900 JAMES      CLERK           7698        950
      7902 FORD       ANALYST         7566       3000
      7934 MILLER     CLERK           7782       1300

12行が選択されました。
リスト1　EMP表の問い合わせ

　見慣れたEMP表のSELECT結果ですが……、誰が上司で、誰が部下？！は一目では分かりづらいと思います。そんなときに使えるのが、階層問い合わせです。例えば、上記の見慣れたSQL文と見慣れた結果を階層問い合わせにすると……。

SQL> select
  2      empno,
  3      lpad(' ',2*(level)) || ename ename,
  4      job,
  5      hiredate,
  6      sal,
  7      comm
  8  from
  9      emp
 10  start with ename = 'KING'
 11  connect by prior empno = mgr;

     EMPNO ENAME                JOB       HIREDATE        SAL     COMM
---------- -------------------- --------- -------- ---------- --------
      7839   KING               PRESIDENT 81-11-17       5000
      7566     JONES            MANAGER   81-04-02       2975
      7902       FORD           ANALYST   81-12-03       3000
      7369         SMITH        CLERK     80-12-17        800
      7698     BLAKE            MANAGER   81-05-01       2850
      7499       ALLEN          SALESMAN  81-02-20       1600      300
      7521       WARD           SALESMAN  81-02-22       1250      500
      7654       MARTIN         SALESMAN  81-09-28       1250     1400
      7844       TURNER         SALESMAN  81-09-08       1500        0
      7900       JAMES          CLERK     81-12-03        950
      7782     CLARK            MANAGER   81-06-09       2450
      7934       MILLER         CLERK     82-01-23       1300

12行が選択されました。
リスト2　EMP表の階層問い合わせ

　誰が上司なのかはすぐ分かりますよね。KING社長をルートとして、JONES、BLAKE、CLARKが各部門のManagerに派生し、以下それぞれの部署の人員がすぐ分かります。

　では、このSQL文をじっくりと見ていきましょう。まず3行目のlpad関数は、整形に使える関数です。分かりやすい例を出しましょう。

SQL> select lpad('+',5,'-') || 'abcde' from dual;

LPAD('-',5
----------
----+abcde
リスト3　lpad関数のサンプル

　この例では、第1引数に指定した文字が5文字目にあり、それまでは第3引数に指定した文字で埋められます。lpad関数の第3引数を指定しないとデフォルトで空白が入ります。

　リスト2に戻って、EMP表の階層構造で考えます。今回の場合、

lpad(' ',2*(level))

としています。第3引数はデフォルトで空白なので

lpad(' ',2*(level),' ')

と同じ結果になります。第2引数は「2*(level)」となっています。

　このlevelというのは階層レベルです。「階層レベル×2」を指定することで、階層が1つ下がると2つずつ空白が増えていくことになります。

　次に10行目のstart with句に'KING'を指定してENAMEが'KING'のものを起点（ルート）に、階層化しています。でも、KINGがルートかどうかなんて、分からない可能性があります。

　ならば、ルートの条件をSQL文で抽出してしまえばよいのです。

SQL> select
  2      empno,
  3      lpad(' ',level*2) || ename ename,
  4      mgr
  5   from
  6      emp
  7  start with ename = ( select ename from emp where mgr is null )
  8  connect by prior empno = mgr
  9  /

     EMPNO ENAME                       MGR
---------- -------------------- ----------
      7839   KING
      7566     JONES                  7839
      7902       FORD                 7566
      7369         SMITH              7902
      7698     BLAKE                  7839
      7499       ALLEN                7698
      7521       WARD                 7698
      7654       MARTIN               7698
      7844       TURNER               7698
      7900       JAMES                7698
      7782     CLARK                  7839
      7934       MILLER               7782

12行が選択されました。
リスト4　ルートの条件をSQL文で抽出した階層問い合わせ

　リスト4の8行目、条件式「connect by prior empno = mgr」で、親子関係を指定します。この例では、EMPNOとMGRを関連付けることにより、上司・部下の関係性を指定しています。（次ページへ続く）

　 1/2

　Index
連載 SQLクリニック（6）
SQLの抽出結果を階層構造で表示するテクニック
Page 1
・階層問い合わせの基本
  Page 2
・表の結合で階層問い合わせを使う
・実行計画を階層問い合わせしてみる


SQLクリニック

TechTargetジャパン

Database Expert フォーラム 新着記事

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）

RSSフィード

＠IT 新着記事

キャリアアップ

- PR -
＠IT Sepcial
→ ＠IT Special ヘ

イベントカレンダー

PickUpイベント

- PR -
もっと見る
- PR -

お勧め求人情報

ホワイトペーパー（TechTargetジャパン）

＠IT Sepcial
→ ＠IT Special ヘ
ソリューションFLASH