共通テーブル式（CTE）で再帰的にデータを取得する（WITH句）

SQL Serverに共通テーブル式（CTE）というのがあり、自己参照で再帰的にデータを取得できることを知ったので、使う機会はあまりない気がしますが、せっかくなのでメモとして残しておきます。

テスト用に以下のテーブルとデータを用意します。

テーブル名「example_tbl」

id	name	parent_id
1	a	NULL
2	b	NULL
3	c	NULL
11	a1	1
12	a2	1
13	a3	1
21	b1	2
22	b2	2
31	c1	3
111	a1-1	11
112	a1-2	11
113	a1-3	11
121	a2-1	12
122	a2-2	12
131	a3-1	13
211	b1-1	21
212	b1-2	21
221	b2-1	22
311	c1-1	31

上記のデータを再帰的に呼び出して、データ毎に階層レベルを追加するSQLは以下のようになります。

WITH alldata(data_id, data_name, data_level) AS (

	SELECT id, name, 0 AS data_level 
	FROM example_tbl 
	WHERE parent_id IS NULL

	UNION ALL

	SELECT id, name, (data_level + 1) AS data_level 
	FROM example_tbl 
	INNER JOIN alldata ON 
	example_tbl.parent_id = alldata.data_id
) 

SELECT * 
FROM alldata 
ORDER BY data_name;

実行結果は以下の通りです。

data_id	data_name	data_level
1	a	0
11	a1	1
111	a1-1	2
112	a1-2	2
113	a1-3	2
12	a2	1
121	a2-1	2
122	a2-2	2
13	a3	1
131	a3-1	2
2	b	0
21	b1	1
211	b1-1	2
212	b1-2	2
22	b2	1
221	b2-1	2
3	c	0
31	c1	1
311	c1-1	2

共通テーブル式で作成したテーブルを結合する際はインデックスとか効かない気がするので、パフォーマンスを求める場合は利用に注意が必要そうですね。

今回メモとして残しておくきっかけが、すべてのテーブルに対して共通テーブル式を用意しておき、ほとんどのSQLでWITHで作成したテーブルを経由してデータを取得しているプロジェクトの改修に携わったからですが、そこまでデータ数が多くないシステムだったからかもしれませんが（1テーブル当たり多くて1000件くらい）、そのシステムは問題なく動いていました。

• COMMON_TABLE_EXPRESSION (TRANSACT-SQL) | Microsoft Docs