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クリニック

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