組織の部署名を、再帰クエリで表示するには

はじめは、何が何だかワケワカランだったけど、やってることが分かったから、忘れないうちに残しとこ。

EW-SYS.INFO

データベースのテーブルに、部署データを入れて、いろいろと使いたいというとき、どんなテーブルを創造するだろう?

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

単純に考えると、部署コードと部署名が一覧になっていればいい。

でも、これは組織変更で、部署名が変わった場合、修正する箇所が多い。

置換作業すれば一瞬ですが・・・便利な機能ですわ。それでも、複数の置換条件で、置換前後の言葉が被っていた場合には、置換する順番を、いちち考えなければ、正しい置換ができなくなります。

単純なのは「こういう結果が欲しい」という出力結果だけにしておきましょう。

なので、十人いれば九人が、こんな感じになると思います。

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

部署コードと部署名があるのは、変わりませんが、親部署コードという列(項目)が増え、行数も増えてしまいました。

でも、これなら「営業一課」が「営業三課」になっても、一箇所だけ変えればよいようになりました。

しかし、欲しい答えは「営業部営業一課営業一係」という部署名なので、「親部署コード」が追加されいるのです。

親部署コードは、その部署の上位部署の部署コードになっています。

その親部署コードを追っていけば、「営業一係」の上位部署は「営業一課」、「営業一課」の上位部署は「営業部」というように辿っていけます。

人間が、自身の目で見て辿るには、非常に面倒くさいかたちになってしまいましたが、この手の処理は、データベースにとっては、さほど負担でもない・・・データ量にもよるのですが、得意な作業な・・・はずです。・・・だったらいいなぁ。

データベースのテーブルから、欲しい情報を得るためにSQL文を書きます。

今の話題で欲しい情報は、長い部署名となりますが、ちょっと汎用的な項目を追加しています。

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

この部署マスター情報(M_Department)というテーブルから、次のような形で情報をえたい。

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

それが、このようなSQL文になった。

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 UID = 4 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値)のレコードを、元となるテーブル「部署マスター情報(M_Department)」から呼び出しています。

親部署コードがないというのは、最上位部署の位置づけってことになります。これ以上、上がないから、親部署コードがNull値になっているという訳です。

さて、コード中で異色なのは、case文の辺りでしょうか。

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

この世の中には「部署名でも、特に表記しなくていい部署があるだろう」ということで、HideFlagという非表示区分を設けましたので、非表示にしたい場合は、部署名を出さない(blankにする)という条件分岐の処理をしています。抽出条件で、非表示区分を指定しないのは、そこで、部署と親部署の関係が切れてしまうことを避けるため。

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


 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

)

union allは、二つの結果を連続させるような意味合いなので、次のSQL文では、一時テーブル(TemporaryTable)と、部署マスター情報(M_Department)を、一時テーブルの部署コードと部署マスター情報の親部署コードを紐づけるという条件で、読み取ろうとしています。この条件のおかげで、先に読んで、一時テーブルに格納されている親部署コード=nullの条件に当てはまる部署が省かれています。

この中で、重要なのは、次の二つ。

  • 深度が一時テーブル側の深度に+1になっていること。
  • 連結部署名が、一時テーブルの連結部署名の後ろに、部署マスター情報の部署名を、追加していること。

ここまでが、一時テーブル(TemporaryTable)をつくるSQL文になります。一時テーブルをつくる過程で、一時テーブルを読み込むので、「再帰」っていうそうです。

さあ!最後は一時テーブルを読むだけ。

 select * from TemporaryTable where UID = 4 order by Code

最後のselect文で、実際に欲しい一時テーブルの項目を指定して読み込んでもいいし、抽出条件を指定して、目的の部署だけに絞って読み込んでもいいし、抽出結果を好きな項目で並べ替えてもいいし、と、お気に召すままに読み込めます。

scroll page top ▲