夏椰の東屋

- お遊び記録 -

ホーム 連絡をする 同期する ( RSS 2.0 ) Login
投稿数  108  : 記事  1  : コメント  3551  : トラックバック  30

ニュース


落書きしてね♪

IAM
僕がとった写真です。
ご自由にお使いください。

フィードメーター - 夏椰の東屋 track feed
広告


記事カテゴリ

書庫

日記カテゴリ

Other Site From Kaya

今日はSQL Server 2008から登場する「hierarchyid」という型で遊んでみました。

 

RTableというお名前で以下のテーブルを作成しました。

idがキーでpidが自分が日もづくデータのidが格納されている状態になります。

id kname pid
0000 社長 NULL
1000 本部長 0000
2000 開発部長 1000
3000 運用部長 1000
3010 社員a 3000
3020 社員b 3000
3030 社員c 3000
2100 開発係長1 2000
2200 開発係長1 2000
2110 社員A 2100
2120 社員B 2100
2130 社員C 2100
2140 社員D 2100

 

 

さて、これに対してSQL Server 2005では、階層的にとる再帰SQLがありました。→see

 

まずは再帰SQLで書いてみます。

 


with q (id, kname, pid, level)
as
(
  select  id, kname, pid, 0 from RTable where pid is null
 union all
 select RTable .id, RTable .kname, RTable .pid, q.level+1 from RTable join q on RTable.pid = q.id
)
SELECT                  id, kname, pid, level
FROM                    q

結果は以下の通りなります。

id kname pid level
0000 社長 NULL 0
1000 本部長 0000 1
2000 開発部長 1000 2
3000 運用部長 1000 2
3010 社員a 3000 3
3020 社員b 3000 3
3030 社員c 3000 3
2100 開発係長1 2000 3
2200 開発係長1 2000 3
2110 社員A 2100 4
2120 社員B 2100 4
2130 社員C 2100 4
2140 社員D 2100 4

さて今度はSQL Server 2008から登場したhierarchyid君を使用して同じ結果を取得してみたいと思います。

 


with q (id, kname, pid, path)
as
(
  select  id, kname, pid,hierarchyid::GetRoot() as root from RTable where pid is null
 union all
 select RTable .id, RTable .kname, RTable .pid, cast( path.ToString() + cast(path.GetLevel() as varchar)+ '/' as hierarchyid) from RTable join q on RTable.pid = q.id
)
SELECT                  id, kname, pid,  path.GetLevel()
FROM                    q

ポイントになるのは再帰クエリでは初期データのレベルを0と書いていましたが

これがhierarcyid::GetRoot()というものに置き換わりました。

これでノードの初期値を設定しています。

次にレベルの加算をしていた部分が長ったらしく書き変わりました。

これはわかりやすく表現すると『/0/1/2/』のように階層のパスを作成して新しいhierarchyidを作成しています。

path.ToString()でそれまでの階層の『/0/1/』を文字列として取得し、今のレベルをpath.GetLevel()で取得して連結し、『/0/1/2』を作成して

最後に/を付加して『/0/1/2/』という文字列を作っています。

 

出力するときもパスからそのhierarchyidのレベルをGetLevel()で取得して表示しています。

結果は一緒なので、割愛♪

ちなみに最後のGetLevel()をToString()に置き換えるとこう出力されます。

 

id kname pid expr1
0000 社長 NULL /
1000 本部長 0000 /0/
2000 開発部長 1000 /0/1/
3000 運用部長 1000 /0/1/
3010 社員a 3000 /0/1/2/
3020 社員b 3000 /0/1/2/
3030 社員c 3000 /0/1/2/
2100 開発係長1 2000 /0/1/2/
2200 開発係長1 2000 /0/1/2/
2110 社員A 2100 /0/1/2/3/
2120 社員B 2100 /0/1/2/3/
2130 社員C 2100 /0/1/2/3/
2140 社員D 2100 /0/1/2/3/

 

さてさて、ここまでがざっくりhierarchyidの使い方って感じです。

次はhierarchyidをテーブルに持ってやろうと思います♪

RTableのpidをhid(hierarchyid)に変えてデータを突っ込んでみました。

#全データ入れるのが面倒だったから適当に入れてみた

0000 社長 /
1000 本部長 /1/
2000 開発部長 /1/1/
3000 運用部長 /1/2/
2100 開発係長1 /1/1/1/
2200 開発係長2 /1/1/21/
3010 社員A /1/2/1/

 

このテーブルからレベルが2のものを取得しようと思うと以下のようなSQLになります。


SELECT                  id, kname, hid
FROM                     HTable
WHERE                   (hid.GetLevel() = 2)
2000 開発部長 /1/1/
3000 運用部長 /1/2/

 

テーブルに入れてあげるとSQLがすっきりしますね(^^)

#本当はIsDescendantをしたかったんだけど、動かすとManagementStudioが落ちるからあきらめた・・・・orz

 

投稿日時 : 2008年3月19日 15:30

コメント

No comments posted yet.

Post Feedback

タイトル
名前
Url:
コメント