再帰 CTE を使って、階層構造のデータを取得する

SQL Server

再帰 CTE の例として書いてみます。
今回は、カラムに親のキー項目を保持するテーブルに対して、再帰 CTE を使ってデータを取得してみようと思います。

テーブル定義/データ投入

create table [dbo].[階層テーブル](
 [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [ParentId] [int] NULL
) 
GO
insert into [階層テーブル] ([Name], [ParentId]) 
values 
('地球', null), 
('日本', 1), 
('日本以外', 1), 
('近畿', 2), 
('大阪', 4), 
('京都', 4), 
('兵庫', 4), 
('関東', 2), 
('東京', 9), 
('神奈川', 9), 
('埼玉', 9), 
('ミラノ', 3), 
('ローマ', 3), 
('ロンドン', 3)
GO


で、最上位(ParentId が null) のデータから、その Id を ParentId として保持しているデータを引っ張って…という風に再帰していくクエリがこちら。

with [CTE] ([Id], [Name], [ParentId], [Level], [Path]) as (
  select 
    [Id], 
    [Name], 
    null, 
    1 as [Level], 
    cast([Name] as nvarchar(4000)) as [Path]
  from 
    [dbo].[階層テーブル] 
  where 
    [ParentId] is null
  union all 
  select 
    [階層テーブル].[Id], 
    [階層テーブル].[Name], 
    [階層テーブル].[ParentId], 
    [CTE].[Level] + 1, 
    [CTE].[Path] + N' - ' + [階層テーブル].[Name]
  from 
    [dbo].[階層テーブル] inner join [CTE] on ( 
      [dbo].[階層テーブル].[ParentId] = [CTE].[Id] 
    ) 
)
select * from [CTE]

Level と Path はわかりやすいようにつけてみました。

次は逆に、指定した Id から ParentId を辿って親レコードを取得するという風に再帰するクエリがこちら。

with [CTE] ([Id], [Name], [ParentId], [Level], [Path]) as (
  select 
    [Id], 
    [Name], 
    [ParentId], 
    1 as [Level], 
    cast([Name] as nvarchar(4000)) as [Path]
  from 
    [dbo].[階層テーブル] 
  where 
    [Id] = 5
  union all 
  select 
    [階層テーブル].[Id], 
    [階層テーブル].[Name], 
    [階層テーブル].[ParentId], 
    [CTE].[Level] + 1, 
    [CTE].[Path] + N' - ' + [階層テーブル].[Name]
  from 
    [dbo].[階層テーブル] inner join [CTE] on ( 
      [dbo].[階層テーブル].[Id] = [CTE].[ParentId] 
    ) 
)
select * from [CTE]


再帰出来る回数は 再帰出来る回数の上限について - お だ のスペース でも書いていますが、既定で 100 になっています。
また、今回のクエリでは所々 CAST していますが、CAST をしていないと、次のようなエラーが出ます。

メッセージ 240、レベル 16、状態 1、行 1
再帰クエリ "CTE" の列 "Path" で、アンカーの型と再帰部分の型が一致していません。


こんなエラーが出た時には、再帰クエリ内で型が違うものを同じカラムとして出力していないか確認してみてください。
今回は、nvarchar(50) 型の値を "+" で結合しているので、サイズが変わっています。

有効桁数、小数点以下桁数、および長さ (Transact-SQL) より引用

nchar 型または nvarchar 型の式が 2 つ連結された場合に得られる式の長さは、基になる 2 つの式の長さを加えた値または 4,000 文字の、いずれか小さい方の値になります。

これを見る限り なんで nvarchar(4000) になるのかは不明…。

全体のクエリはこちら

create table [dbo].[階層テーブル](
 [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [ParentId] [int] NULL
) 
GO
insert into [階層テーブル] ([Name], [ParentId]) 
values 
('地球', null), 
('日本', 1), 
('日本以外', 1), 
('近畿', 2), 
('大阪', 4), 
('京都', 4), 
('兵庫', 4), 
('関東', 2), 
('東京', 9), 
('神奈川', 9), 
('埼玉', 9), 
('ミラノ', 3), 
('ローマ', 3), 
('ロンドン', 3)
GO
select * from [dbo].[階層テーブル]
GO
with [CTE] ([Id], [Name], [ParentId], [Level], [Path]) as (
  select 
    [Id], 
    [Name], 
    null, 
    1 as [Level], 
    cast([Name] as nvarchar(4000)) as [Path]
  from 
    [dbo].[階層テーブル] 
  where 
    [ParentId] is null
  union all 
  select 
    [階層テーブル].[Id], 
    [階層テーブル].[Name], 
    [階層テーブル].[ParentId], 
    [CTE].[Level] + 1, 
    [CTE].[Path] + N' - ' + [階層テーブル].[Name]
  from 
    [dbo].[階層テーブル] inner join [CTE] on ( 
      [dbo].[階層テーブル].[ParentId] = [CTE].[Id] 
    ) 
)
select * from [CTE] 
GO
with [CTE] ([Id], [Name], [ParentId], [Level], [Path]) as (
  select 
    [Id], 
    [Name], 
    [ParentId], 
    1 as [Level], 
    cast([Name] as nvarchar(4000)) as [Path]
  from 
    [dbo].[階層テーブル] 
  where 
    [Id] = 5
  union all 
  select 
    [階層テーブル].[Id], 
    [階層テーブル].[Name], 
    [階層テーブル].[ParentId], 
    [CTE].[Level] + 1, 
    [CTE].[Path] + N' - ' + [階層テーブル].[Name]
  from 
    [dbo].[階層テーブル] inner join [CTE] on ( 
      [dbo].[階層テーブル].[Id] = [CTE].[ParentId] 
    ) 
)
select * from [CTE] 
GO
drop table [dbo].[階層テーブル]
GO