【SQLレシピ】階層を持つテーブルでルートから指定した子までの列を連結する

SQLで階層を持つテーブルでルートから指定した子までの列を連結したい！
「日本 - 関東 - 東京」みたいに「東京」を指定したらフルパスを取得する的な！

概要

この記事では、SQLで階層を持つテーブルでルートから指定した子までの列を連結する手順を掲載する。

仕様書

環境

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が属する親の名前と自分の名前をルートから並べえて連結するクエリを紹介する。
レコードの間には - を挿入する。

下記はSQLite。

WITH hierarchy AS (
  SELECT
    id,
    parent_id,
    CAST(name AS TEXT) AS path
  FROM
    base
  WHERE
    parent_id = 0
  UNION ALL
  SELECT
    t.id,
    t.parent_id,
    h.path || '-' || t.name
  FROM
    base t
    INNER JOIN hierarchy h ON t.parent_id = h.id
)
SELECT path FROM hierarchy WHERE id = 16;

下記はSQL Server。

WITH hierarchy AS (
  SELECT
    id,
    parent_id,
    CASTname AS VARCHAR(255)) AS path
  FROM
    base
  WHERE
    parent_id = 0
  UNION ALL
  SELECT
    t.id,
    t.parent_id,
    CAST(h.path + '-' + CAST(t.name AS VARCHAR(255)) AS VARCHAR(255))
  FROM
    base t
    INNER JOIN hierarchy h ON t.parent_id = h.id
)
SELECT path FROM hierarchy WHERE id = 16;

クエリを実行すると下記のように名前が連結されて出力される。

path
日本 - 関東 - 東京

SQLiteとSQL Serverの違いは型のCASTの部分と文字列の連結の部分。今回の様に文字列型の列を連結する分にはCASTは不要なんだけども、整数型などの文字列型以外を連結する場合は文字列型にCASTする必要がある。メモとして残してる。

まとめ（感想文）

このテーブルの様に親のidを保存する列を作って階層を表現する場合、親を辿る際にループしないように注意！