SQL Server 2005で再帰クエリ

DB

前から一度使ってみたいなぁと思ってた再帰クエリ。おもしろいです。SQL Server 2005で使えることをもっと早く知ってればなぁ。

一瞬BeanFactoryの継承関係を例にしようかなーと思ったけど調べるのがめんどくさくなったのでパス。Listの具象クラスの継承関係を子供（Stack）から親にたどってみることにしました。
テーブルとデータを用意。

create table Class (
  Id bigint primary key,
  [Name] varchar(50) not null,
  ParentId bigint,
);

insert into Class values(1, 'Object', null);
insert into Class values(2, 'AbstractCollection', 1);
insert into Class values(3, 'AbstractList', 2);
insert into Class values(4, 'AbstractSequentialList', 3);
insert into Class values(5, 'LinkedList', 4);
insert into Class values(6, 'ArrayList', 3);
insert into Class values(7, 'AttributeList', 6);
insert into Class values(8, 'RoleList', 6);
insert into Class values(9, 'RoleUnresolvedList', 6);
insert into Class values(10, 'Vector', 3);
insert into Class values(11, 'Stack', 10);

再帰クエリの実行！

declare @Name varchar(50);
set @Name = 'Stack';

with Hierarchy (Id, [Name], ParentId, HierarchyLevel) as (
    select C.Id, C.[Name], C.ParentId, 0
    from Class C
    where [Name] = @Name
    union all
    select C.Id, C.[Name], C.ParentId, Hierarchy.HierarchyLevel + 1
    from Class C
    inner join Hierarchy on (C.Id = Hierarchy.ParentId)
)
select substring('+-------------------', 1, HierarchyLevel * 4) + [Name] from Hierarchy;

仕組みがいまいちわかってないのですが、

  • with
  • union all
  • inner join

が肝っぽいです。

結果

Stack
+---Vector
+-------AbstractList
+-----------AbstractCollection
+---------------Object

条件に合わせて任意の長さの文字列を結合する方法がわからなかったのでsubstringつかってインデントしてみました。


5.0になってListの具象クラス増えましたね。ArrayListを継承したクラスがあってびっくり。そうなんだー。でも継承がふさわしいの？と気になってしまいます。