今日は再帰SQLをやってみます。
ベースとなるテーブルは部課チーム名が入ったテーブルで
PIDに設定されている値をIDにもつレコードが必ずある状態です。
bushoテーブル
| id |
name |
pid |
| 1 |
システム部 |
NULL |
| 2 |
営業部 |
NULL |
| 3 |
システム課 |
1 |
| 4 |
運用課 |
1 |
| 5 |
サーバチーム |
4 |
| 6 |
ホストチーム |
4 |
?
SQLはこんなんしてみました。
WITH bushoCTE(pid, id, name, level)
AS
(
/* ベースとなる初期レコード取得SQL */
SELECT
? pid,
? id,
? name,
? /* レベルの初期値は0 */
? 0
FROM
? busho a
WHERE
? /*? 自分のIDがどのレコードの親にもなっていないデータを取得 ＝ 一番最下層のレコードを取得*/
? not exists ( select * from busho b where b.pid = a.id )
UNION ALL
/* ベースを元にして取得した親のレコード */
SELECT
? b.pid,
? c.id,
? /* 親の部課チーム名＋今まで取得した部課チーム名*/
? b.name + ' ' + c.name,
? /* レベルは1加算 */
? level + 1
FROM
? /* 自分自身(bushoCTE)とJOINすることで再帰を実現 */
? busho b inner join bushoCTE c ON c.pid = b.id
)
SELECT * FROM bushoCTE
取得結果
| pid |
id |
name |
level |
| NULL |
2 |
営業部 |
0 |
| 1 |
3 |
システム課 |
0 |
| 4 |
5 |
サーバチーム |
0 |
| 4 |
6 |
ホストチーム |
0 |
| 1 |
6 |
運用課 ホストチーム |
1 |
| NULL |
6 |
システム部 運用課 ホストチーム |
2 |
| 1 |
5 |
運用課 サーバチーム |
1 |
| NULL |
5 |
システム部 運用課 サーバチーム |
2 |
| NULL |
3 |
システム部 システム課 |
1 |
(1) まずレベル0のレコードが取得されます。
取得したSQLはWITH句に指定しているUNION ALLの前に記述されたSELECTです。
(2) (1)で取得したレコードの1つに対し、SQLのWITH句に指定しているUNION ALLの後に書かれたSQLを実行し、レコードを追加します。
(3) (2)で取得したレコードの1つに対し、SQLのWITH句に・・・(以下略)
?
と、まぁこんな感じです。( おおざっぱすぎ(^^;? )
ポイントはUNION ALLの後に書かれたSQLでWITHで指定したクエリとJOINしている事です。
UNION ALLの前は(1)で書いたレベル0のレコード(ベースのレコード)になるようにしています。
あとはベースレコードに対して(2)を繰り返し行うだけですね。
1つだけ取り出して見せると以下の順になります。
(1)
↓
(2)
↓
(3)
| NULL |
6 |
システム部 運用課 ホストチーム |
2 |
?
?
・・・・う～ん、うまく伝えられません。ごめんなさい。
?
これ使ってPIDがNULLのものだけをとると、部課チーム名の一覧が出来ちゃいますよ～。
なかなかわかりにくいですが、機会があれば遊んであげて活用してあげてくださいませ。
?