再帰クエリで木構造のデータを取得する
この投稿は インタープリズムは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
階層の数だけ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]
再帰クエリではUNION ALL 以下のSELECT文の結果が空になるまで繰り返し実行されるため
階層の数を指定する必要がありません。
よって、JOINのような問題が生じることもありません。
注意点
再帰クエリは標準sqlの共通表式の一環として定義されていますが、
MySQLなどサポートしていないDBMSも存在しますので注意が必要です。
(MySQL 8.0 ではサポートされるらしいですが……)
インタープリズムのページ
