2020-01-07 更新

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

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

目次

  • データ
  • コード
  • 実行結果
  • 備考
  • 参考リンク

データ

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

テーブル名「example_tbl」

idnameparent_id
1aNULL
2bNULL
3cNULL
11a11
12a21
13a31
21b12
22b22
31c13
111a1-111
112a1-211
113a1-311
121a2-112
122a2-212
131a3-113
211b1-121
212b1-221
221b2-122
311c1-131

コード

上記のデータを再帰的に呼び出して、データ毎に階層レベルを追加する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_iddata_namedata_level
1a0
11a11
111a1-12
112a1-22
113a1-32
12a21
121a2-12
122a2-22
13a31
131a3-12
2b0
21b11
211b1-12
212b1-22
22b21
221b2-12
3c0
31c11
311c1-12

備考

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

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

参考リンク

SQL Server】関連記事