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

ネコニウム研究所

PCを利用したモノづくりに関連する情報や超個人的なナレッジを掲載するブログ

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

2023-6-26 | , ,

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 大阪府

まとめ(感想文)

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