再帰クエリで木構造のデータを取得する

この投稿は インタープリズムはAdvent Calendarを愛しています。世界中のだれよりも。 Advent Calendar 2017の10日目 の記事です。

yoshiといいます。
今回は再帰クエリを用いて木構造のデータを取得する方法について書いてみました。
使用したデータベースは Microsoft SQL Server 2017 Express です。

木構造のデータ

今回使用するデータは、次のような構造のデータとします。

     Id     |   ParentId
------------+------------
 1          | (NULL)
 2          | 1
 3          | 1
 4          | 2
 5          | 2
 6          | 3
 7          | 3
 8          | 4
 9          | 4
 10         | 5

CREATE TABLE [Node](
    [Id] int NOT NULL,
    [ParentId]  int NULL
)

INSERT INTO [dbo].[Node] VALUES
    (1, null)
    ,(2, 1)
    ,(3, 1)
    ,(4, 2)
    ,(5, 2)
    ,(6, 3)
    ,(7, 3)
    ,(8, 4)
    ,(9, 4)
    ,(10, 5)

JOINを使用して子の一覧を取得する

単純にJOINを使用して取得しようとすると、以下のようなクエリになってしまいます。

SELECT
        n1.Id, n2.Id, n3.Id
    FROM
        [dbo].[Node] AS n1
        LEFT OUTER JOIN [dbo].[Node] AS n2
            ON n1.[Id] = n2.[ParentId]
        LEFT OUTER JOIN [dbo].[Node] AS n3
            ON n2.[Id] = n3.[ParentId]
    WHERE
        n1.Id = 2
/*
     Id     |     Id     |     Id     
------------+------------+-------------
 2          | 4          | 8
 2          | 4          | 9
 2          | 4          | 10
*/

階層の数だけJOINする必要があるので、

取得したい階層の数が変わるごとにSQLを修正する必要がある
「指定したIDの子すべてを取得する」など、取得する階層の数が不明な場合に対応できない

と言った問題があります。

再帰クエリを使用して取得する

しかし、再帰クエリを使用することにより上記の問題を解決することができます。

WITH [temp]([Id], [ParentId], [Depth])
AS
(
    SELECT
            n.[Id]
            ,n.[ParentId]
            ,1 AS [Depth]
        FROM
            [dbo].[Node] AS n
        WHERE
            n.[Id] = 2
    UNION ALL
    SELECT
            n.[Id]
            ,n.[ParentId]
            ,t.[Depth] + 1 AS [Depth]
        FROM
            [dbo].[Node] AS n
            INNER JOIN [temp] AS t
                ON n.[ParentId] = t.[Id]
)

SELECT
        *
    FROM
        [temp]

/*
     Id     |   ParentId|   Depth
------------+-----------+-------------
 2          | 1         | 1
 4          | 2         | 2
 5          | 2         | 2
 8          | 4         | 3
 9          | 4         | 3
 10         | 5         | 3
*/