【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 | 大阪府 |
まとめ(感想文)
組織でグループが属する部を選択したい場合とかに使えるかもね!