再帰クエリって何さ!?

組織名を再帰クエリで抽出する

「再帰」って、失敗すると無限ループに陥りそうで、なんか怖いです。

EW-SYS.INFO

テーブル設計

 組織内の部署情報をデータベース化したい場合、そのテーブル構造をどの様に設計しましょう?
 前提として、各部署の情報には「部署コード」と「部署名」が在るとし、各部署は、階層構造で組織化されています。
 例)営業部 > 営業一課 > 営業一係

パターン1

部署コード部署名
1営業部営業一課営業一係
2営業部営業一課営業二係

 「部署コード」と「部署名」を並べる。

 すっきり一覧表になりました。
 しかし、組織変更などによる部署名称の変更の際に、変更の対象となるレコードが複数行存在してしまいます。
 「置換機能」で置き換えてしまえば、作業は簡単に終わりますが、正しく更新されているか、余分に書き換えられていないかなど、すべての行を確認する作業が大変です。

パターン2

部署コード親部署コード部署名
1会社
21営業部
32営業一課
43営業一係
53営業二係
61営業二課

 最小組織単位で「部署コード」と「部署名」を並べ、組織間の親子関係を持たせる。

 最小組織単位ですから、行数が増えてしまいましたが、組織変更などによる部署名称の変更の際には、変更の対象となるレコードは1行で済みます。
 しかし、部署名として欲しい答えは「営業部営業一課営業一係」と連結されたものです。
 そのために「親部署コード」による親子関係を示す情報を追加しました。表では「営業一係」の親部署は「営業コード」が3の「営業一課」で、「営業一課」の親部署は「営業コード」が2の「営業部」となります。
 人間の目で辿るのは、非常に面倒ですが、データベースにとっては得意な作業です。そもそも、そのためにある様なものです。

テーブルを定義

 テーブル設計のパターン2に則り、テーブルを定義します。
 テーブル名は「部署マスター」として、項目の定義の際に、汎用的な項目を追加してみました。

部署コード親部署コード任意コード(※1)部署名非表示区分(※2)
UIDParentUIDCodeNameHideFlag
1Null1会社True
2111営業部False
32111営業一課False
431111営業一係False
531112営業二係False
62112営業二課False
M_Department(組織マスター情報)の内容

(※1)「任意コード」は、データベース内の部署コードとは異なり、利用者が任意に付けられる人間側の「部署コード」として追加しました。
(※2)「非表示区分」は、組織名を表に出す必要のない組織の名称を連結表示させないための区分として追加しました。

テーブルから抽出したい情報

 定義したテーブルから、次のような形で情報を抽出したいと思います。

階層深度部署コード任意コード部署名親部署コード連結名
DepthUIDCodeNameParentUIDFullName
111会社Null
2211営業部1営業部
33111営業一課2営業部営業一課
441111営業一係3営業部営業一課営業一係
451112営業二係3営業部営業一課営業二係
36112営業二課2営業部営業二課

さっそくSQL文(全文)

 欲しい情報を抽出する再帰クエリのSQL文はこちらです。
 任意コードが1111の「営業一係」の情報を抽出しています。

with TemporaryTable (

 Depth
,UID
,Code
,Name
,ParentUID
,FullName

) as (

 select
 1 AS Depth
,UID
,Code
,Name as Base_Name
,ParentUID
,case
    when HideFlag = 0 then cast(Name as nvarchar(max))
    when HideFlag = 1 then ''
    else ''
 end as FullName
 from
 M_Department
 where
 ParentUID is NULL

 union all

 select
 Parent.DEPTH + 1
,Child.UID
,Child.Code
,Child.Name 
,Child.ParentUID
,case
    when HideFlag = 0 then Parent.FullName + Child.Name
    when HideFlag = 1 then ''
    else ''
 end
 from
 TemporaryTable Parent
,M_Department Child
 where
 Parent.UID = Child.ParentUID

)

 select * from TemporaryTable where Code = '1111' order by Code

解説

 ここからは、このSQL文を見ただけで理解できる方には用がない解説となっています。

with TemporaryTable (

 Depth
,UID
,Code
,Name
,ParentUID
,FullName

) as (

 まずは、「TemporaryTable」と、勝手に名付けた一時テーブルに、抽出する項目を定義しています。


) as (

 select
 1 AS Depth
,UID
,Code
,Name as Base_Name
,ParentUID
,case
    when HideFlag = 0 then cast(Name as nvarchar(max))
    when HideFlag = 1 then ''
    else ''
 end as FullName
 from
 M_Department
 where
 ParentUID is NULL

 union all

 続く、このselect文では、親部署コードがない(Null値)のレコードを、元となるテーブルから抽出しています。
 親部署コードがないというのは、組織の最上位ってことになります。上がないから親部署コードをNull値(空っぽ)にしています。

 さて、このコード中で異色なのは、case文の辺りですが、これは非表示区分による条件分岐で、組織名を表示する場合と表示しない場合に分けています。

,case
    when HideFlag = 0 then cast(Name as nvarchar(max))
    when HideFlag = 1 then ''
    else ''
 end as FullName

 その条件分岐の中に、あえてnvarchar(max)と型宣言している箇所があります。これは、数多あるデータベースの仕様によって要不要が変わります。
 後に続くのSQL文の中で、ここに部署名(Name)を連結していくのですが、一部のデータベースでは「連結していく項目の型が判らないからできない!」とエラーになってしまいます。そのため「この項目はnvarchar(max)という型ですよ」とcastしているのです。

 union allは、二つの結果を連結させます。


 union all

 select
 Parent.DEPTH + 1
,Child.UID
,Child.Code
,Child.Name 
,Child.ParentUID
,case
    when HideFlag = 0 then Parent.FullName + Child.Name
    when HideFlag = 1 then ''
    else ''
 end
 from
 TemporaryTable Parent
,M_Department Child
 where
 Parent.UID = Child.ParentUID

)

 このSQL文では、部署マスターの親部署コードと一時テーブルの部署コードを紐づけして、部署名を連結しながら抽出しています。親が主になるので、親に帰属する子のレコード(一時テーブル(親)の部署コードを親部署コードにもつ部署マスター(子)のレコード)を抽出するようになっています。帰属する子が無くなるまで再帰してます。
 ここまでが一時テーブルの情報を作成しています。

 さあ!最後は一時テーブルから欲しい情報を抽出するだけです。

 select * from TemporaryTable where Code = '1111' order by Code

 最後のselect文では、実際に欲しい一時テーブルの必要な項目名だけ抽出しても良いですし、抽出条件を指定して、目的の部署の情報だけに絞って抽出しても良いです。

 「再帰」とは「自分で自分を呼び出す」ということです。
 永遠に自分で自分を呼び続けたら無限ループ。「再起不能」って「再帰不能」ってことですね。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください