(普通の) 再帰 CTE を hierarchyid で

SQL Server 2008 に実装されている hierarchyid 型は、経路列挙モデルをベースにした型なので、再帰 CTE を置き換えることができる。
例えば、
SQL Server 2005で再帰クエリ - taediumの日記
の再帰 CTE を hierarchyid で実現してみる。

使用するテーブル

CREATE TABLE [dbo].[Classes](
    [id] [hierarchyid] NOT NULL,
    [name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE はデザイナ (？) で作ってスクリプト化しただけ。
正直、CREATE TABLE 文とかよく分かりません (何

データの登録

DECLARE @root AS hierarchyid = hierarchyid::GetRoot();
DECLARE @a_collection AS hierarchyid = @root.GetDescendant(NULL, NULL);
DECLARE @a_list AS hierarchyid = @a_collection.GetDescendant(NULL, NULL);
DECLARE @a_seq_list AS hierarchyid = @a_list.GetDescendant(NULL, NULL);
DECLARE @linked_list AS hierarchyid = @a_seq_list.GetDescendant(NULL, NULL);
DECLARE @array_list AS hierarchyid = @a_list.GetDescendant(@a_seq_list, NULL);
DECLARE @attr_list AS hierarchyid = @array_list.GetDescendant(NULL, NULL);
DECLARE @role_list AS hierarchyid = @array_list.GetDescendant(@attr_list, NULL);
DECLARE @role_unresolved_list AS hierarchyid = @array_list.GetDescendant(@role_list, NULL);
DECLARE @vector AS hierarchyid = @a_list.GetDescendant(@array_list, NULL);
DECLARE @stack AS hierarchyid = @vector.GetDescendant(NULL, NULL);

INSERT INTO Classes VALUES
    (@root, 'Object')
  , (@a_collection, 'AbstractCollection')
  , (@a_list, 'AbstractList')
  , (@a_seq_list, 'AbstractSequentialList')
  , (@linked_list, 'LinkedList')
  , (@array_list, 'ArrayList')
  , (@attr_list, 'AttributeList')
  , (@role_list, 'RoleList')
  , (@role_unresolved_list, 'RoleUnresolvedList')
  , (@vector, 'Vector')
  , (@stack, 'Stack')
;

全部展開して一回の INSERT で済ますこともできる (と思う) けど、超絶に読みにくくなるのでいったん変数に格納しておく。

ちなみにこの状態で

SELECT
    id
  , id.ToString() AS id_str
  , name
FROM
    Class

この SQL を実行すると、

id	id_str	name
0x	/	Object
0x58	/1/	AbstractCollection
0x5AC0	/1/1/	AbstractList
0x5AD6	/1/1/1/	AbstractSequentialList
0x5AD6B0	/1/1/1/1/	LinkedList
0x5ADA	/1/1/2/	ArrayList
0x5ADAB0	/1/1/2/1/	AttributeList
0x5ADAD0	/1/1/2/2/	RoleList
0x5ADAF0	/1/1/2/3/	RoleUnresolvedList
0x5ADE	/1/1/3/	Vector
0x5ADEB0	/1/1/3/1/	Stack

こうなる。

SELECT

DECLARE @name AS varchar(50) = 'Stack';
DECLARE @str AS varchar(max)
  = (SELECT id.ToString() FROM Classes WHERE name = @name);

SELECT
    SUBSTRING('+-------------------', 1, Class.id.GetLevel() * 4) + Class.name
FROM
    Classes Class
WHERE
    @str LIKE id.ToString() + '%'
ORDER BY
    id.GetLevel()
;

で、肝心の SELECT 文は再帰 CTE を使ってないから再帰 CTE が分からなくても読める・・・はず！
チョーシに乗って SUBSTRING を使わない版とかも書いてみました。

DECLARE @name AS varchar(50) = 'Stack';
DECLARE @str AS varchar(max)
  = (SELECT id.ToString() FROM Classes WHERE name = @name);

SELECT
    CASE id.GetLevel()
    WHEN 0 THEN Class.name
           ELSE '+' + REPLICATE('-', id.GetLevel() * 4 - 1) + Class.name
    END
FROM
    Classes Class
WHERE
    @str LIKE id.ToString() + '%'
ORDER BY
    id.GetLevel()
;

hierarchyid 型楽しいですね！

経路列挙モデルをもっと！

おなじみ、リレーショナル・データベースの世界のSQLで木と階層構造のデータを扱う（２）―― 経路列挙モデルをどうぞ。