【SQLレシピ】階層を持つテーブルで指定したレコードから親のレコードをルートまでSELECTする

SQLで階層を持つテーブルで指定したレコードから親のレコードをルートまでSELECTしたい！
「大阪」を指定したら「大阪」「近畿」「日本」が選択される的な！

概要

この記事では、SQLで階層を持つテーブルで指定したレコードから親のレコードをルートまでSELECTする手順を掲載する。

仕様書

環境

SQLite 3.35.5
SQL Server 15.0.2000

手順書

下表のようを「国～方面～都道府県名」を管理してるテーブルbaseを例にする。

列名	データ型	備考
id	INTEGER	ユニークな連番
parent_id	INTEGER	親のid。0はルート。
name	TEXT	名前

登録してるデータは下記のとおり。

id	parent_id	name
1	0	日本
2	1	北海道・東北
3	2	北海道
4	2	青森県
5	2	岩手県
6	2	宮城県
7	2	秋田県
8	2	山形県
9	2	福島県
10	1	関東
11	10	茨城県
12	10	栃木県
13	10	群馬県
14	10	埼玉県
15	10	千葉県
16	10	東京都
17	10	神奈川県
18	1	中部
19	18	新潟県
20	18	富山県
21	18	石川県
22	18	福井県
23	18	山梨県
24	18	長野県
25	18	岐阜県
26	18	静岡県
27	18	愛知県
28	1	近畿
29	28	三重県
30	28	滋賀県
31	28	京都府
32	28	大阪府
33	28	兵庫県
34	28	奈良県
35	28	和歌山県
36	1	中国
37	36	鳥取県
38	36	島根県
39	36	岡山県
40	36	広島県
41	36	山口県
42	1	四国
43	42	徳島県
44	42	香川県
45	42	愛媛県
46	42	高知県
47	1	九州・沖縄
48	47	福岡県
49	47	佐賀県
50	47	長崎県
51	47	熊本県
52	47	大分県
53	47	宮崎県
54	47	鹿児島県
55	47	沖縄県

今回は、「大阪」のidを指定して、指定したidのレコードとそのidの親のレコードをルートまで選択するクエリを紹介する。

SQLiteとSQL Server共通。

WITH hierarchy AS (
  SELECT
    id,
    parent_id,
    name
  FROM
    base
  WHERE
    id = 2
  UNION ALL
  SELECT
    t.id,
    t.parent_id,
    t.name
  FROM
    base t
    INNER JOIN hierarchy h ON h.id = t.parent_id
)
SELECT * FROM hierarchy;

クエリを実行すると下記のように出力される。

id	parent_id	name
1	0	日本
28	1	近畿
32	28	大阪府

まとめ（感想文）

組織でグループが属する部を選択したい場合とかに使えるかもね！