夏椰の東屋

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

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

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

さて、これに対して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

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

さて今度は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()に置き換えるとこう出力されます。

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

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

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

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

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

SELECT                  id, kname, hid
FROM                     HTable
WHERE                   (hid.GetLevel() = 2)

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

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