かずきのBlog@hatena

日本マイクロソフトに勤めています。XAML + C#の組み合わせをメインに、たまにASP.NETやJavaなどの.NET系以外のことも書いています。掲載内容は個人の見解であり、所属する企業を代表するものではありません。

SQL Databaseでツリー構造扱ってみよう

ちょっとツリー構造をRDBで扱わないといけなくなってしまったのでメモメモ。とりあえず、お試し用にWindows Azure SQL Database上に以下のようなファイルシステムを表すテーブルを用意しました。ローカルにDB持たなくていい世界って素晴らしいですね。

ブラウザからGUIでぽちぽちっとテーブルが定義できるのはSQL弱者の私に優しくて素敵です。

データ投入

これもブラウザからGUIでぱぱっとね

SQLで再帰!

ということで、上のようなデータからC\Program Files\VS2012みたいな文字列作りたいじゃないですか。PostgreSQLとかだと再帰はwith recursive〜でやるみたいなんですがSQL Server系はwithでまかなってるのかな?この差異は、あまりつめて調べてないのであとで確認しないと。
書き方は以下の通り

with 一時テーブル名(*列の定義*) as
(
  select
    *列の定義*
  from テーブル
  union all
    select
      *列の定義*
    from テーブル t1
    join テーブル t2 on t2.id = t1.おやのid
)
select
  *
from
  一時テーブル名;

こんなノリらしい。ポイントは*列の定義*にあたる部分の型と数をきちっと合わせること。実際にあてはめてみるとこんな感じ。

with tree (id, name, path, depth) as
(
  select
    id,
    name,
    name,
    1
  from FileSystem
  where id = 1 /* Cをルートに */
  union all
    select
      fs.id,
      fs.name,
      cast(t.path + '\' + fs.name as nvarchar(50)),
      t.depth + 1
    from FileSystem fs
    join tree t on t.id = fs.parent_id
)
select
  id,
  replicate('  ', depth) + name as name,
  path,
  depth
from tree
order by path /* SQLは順序保証されないので並べ替えは指定しておいたほうがいいよね */

実行結果は素敵な感じになります。SQL弱者でもなんとかできたっ…!