データベースのテーブルに、部署データを入れて、いろいろと使いたいというとき、どんなテーブルを創造するだろう?
部署コード | 部署名 |
1 | 営業部営業一課営業一係 |
2 | 営業部営業一課営業二係 |
単純に考えると、部署コードと部署名が一覧になっていればいい。
でも、これは組織変更で、部署名が変わった場合、修正する箇所が多い。
置換作業すれば一瞬ですが・・・便利な機能ですわ。それでも、複数の置換条件で、置換前後の言葉が被っていた場合には、置換する順番を、いちち考えなければ、正しい置換ができなくなります。
単純なのは「こういう結果が欲しい」という出力結果だけにしておきましょう。
なので、十人いれば九人が、こんな感じになると思います。
部署コード | 親部署コード | 部署名 |
1 | 会社 | |
2 | 1 | 営業部 |
3 | 2 | 営業一課 |
4 | 3 | 営業一係 |
5 | 3 | 営業二係 |
6 | 1 | 営業二課 |
部署コードと部署名があるのは、変わりませんが、親部署コードという列(項目)が増え、行数も増えてしまいました。
でも、これなら「営業一課」が「営業三課」になっても、一箇所だけ変えればよいようになりました。
しかし、欲しい答えは「営業部営業一課営業一係」という部署名なので、「親部署コード」が追加されいるのです。
親部署コードは、その部署の上位部署の部署コードになっています。
その親部署コードを追っていけば、「営業一係」の上位部署は「営業一課」、「営業一課」の上位部署は「営業部」というように辿っていけます。
人間が、自身の目で見て辿るには、非常に面倒くさいかたちになってしまいましたが、この手の処理は、データベースにとっては、さほど負担でもない・・・データ量にもよるのですが、得意な作業な・・・はずです。・・・だったらいいなぁ。
データベースのテーブルから、欲しい情報を得るためにSQL文を書きます。
今の話題で欲しい情報は、長い部署名となりますが、ちょっと汎用的な項目を追加しています。
部署コード | 親部署コード | 任意コード | 部署名 | 非表示区分 |
UID | ParentUID | Code | Name | HideFlag |
1 | Null | 1 | 会社 | True |
2 | 1 | 11 | 営業部 | False |
3 | 2 | 111 | 営業一課 | False |
4 | 3 | 1111 | 営業一係 | False |
5 | 3 | 1112 | 営業二係 | False |
6 | 2 | 112 | 営業二課 | False |
この部署マスター情報(M_Department)というテーブルから、次のような形で情報をえたい。
階層深度 | 部署コード | 任意コード | 部署名 | 親部署コード | 連結名 |
Depth | UID | Code | Name | ParentUID | FullName |
1 | 1 | 1 | 会社 | Null | blank |
2 | 2 | 11 | 営業部 | 1 | 営業部 |
3 | 3 | 111 | 営業一課 | 2 | 営業部営業一課 |
4 | 4 | 1111 | 営業一係 | 3 | 営業部営業一課営業一係 |
4 | 5 | 1112 | 営業二係 | 3 | 営業部営業一課営業二係 |
3 | 6 | 112 | 営業二課 | 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文で、実際に欲しい一時テーブルの項目を指定して読み込んでもいいし、抽出条件を指定して、目的の部署だけに絞って読み込んでもいいし、抽出結果を好きな項目で並べ替えてもいいし、と、お気に召すままに読み込めます。