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は順序保証されないので並べ替えは指定しておいたほうがいいよね */