回答編集履歴
2
階層レベルが経路情報であることを記載しました。2階層上のレコードの出力例を記載しました。
answer
CHANGED
|
@@ -1,4 +1,4 @@
|
|
|
1
|
-
単純な方法としては、以下のような階層レベルを管理するテーブルを作成する方法が考えられます。
|
|
1
|
+
単純な方法としては、以下のような階層レベル(経路情報)を管理するテーブルを作成する方法が考えられます。
|
|
2
2
|
```SQL
|
|
3
3
|
create table 階層レベル (ID int, レベル int, 上位ID int);
|
|
4
4
|
```
|
|
@@ -76,4 +76,30 @@
|
|
|
76
76
|
|
|
77
77
|
なお、以下のデメリットがあります。
|
|
78
78
|
- 冗長なデータを持つ必要がある。
|
|
79
|
-
- 行数が階層の深さなどに依存して多くなる。
|
|
79
|
+
- 行数が階層の深さなどに依存して多くなる。
|
|
80
|
+
|
|
81
|
+
【補足】
|
|
82
|
+
2階層以上の場合の出力例について説明します。
|
|
83
|
+
仮に質問の階層構造のテーブルのBのフラグが1で、Cのフラグが0の場合には以下のようにC、D、Fの上位階層の情報が表示できます。
|
|
84
|
+
|ID|名称|親ID|フラグ|
|
|
85
|
+
|:--|:--:|--:|--:|
|
|
86
|
+
|1|A|null|0|
|
|
87
|
+
|2|B|1|**1**|
|
|
88
|
+
|3|C|2|**0**|
|
|
89
|
+
|4|D|3|0|
|
|
90
|
+
|5|E|1|1|
|
|
91
|
+
|6|F|5|0|
|
|
92
|
+
|7|G|1|0|
|
|
93
|
+
|
|
94
|
+
```SQL
|
|
95
|
+
select * from 階層レベル a inner join 階層構造のテーブル b on a.上位ID=b.ID inner join 階層構造のテーブル c on a.ID=c.ID where b.フラグ=1 and a.ID<>b.ID;
|
|
96
|
+
<= a(階層レベルの情報) => <==== b(フラグが1の名称) ===> <=== c(自レコードの情報) ===>
|
|
97
|
+
+------+--------+--------+------+------+------+--------+------+------+------+--------+
|
|
98
|
+
| ID | レベル | 上位ID | ID | 名称 | 親ID | フラグ | ID | 名称 | 親ID | フラグ |
|
|
99
|
+
+------+--------+--------+------+------+------+--------+------+------+------+--------+
|
|
100
|
+
| 3 | 2 | 2 | 2 | B | 1 | 1 | 3 | C | 2 | 0 |
|
|
101
|
+
| 4 | 2 | 2 | 2 | B | 1 | 1 | 4 | D | 3 | 0 |
|
|
102
|
+
| 6 | 2 | 5 | 5 | E | 1 | 1 | 6 | F | 5 | 0 |
|
|
103
|
+
+------+--------+--------+------+------+------+--------+------+------+------+--------+
|
|
104
|
+
```
|
|
105
|
+
※わかりやすいように、階層構造のテーブルの自レコードの情報をcとして追加しました。
|
1
質問に合わせたSQLを記載しました。また質問で利用しやすいようinsert文を修正しました。
answer
CHANGED
|
@@ -4,23 +4,24 @@
|
|
|
4
4
|
```
|
|
5
5
|
データが追加されたときは、親IDのデータ群をレベル+1として格納します。
|
|
6
6
|
```SQL
|
|
7
|
-
insert into 階層レベル values(<自分のID>,0,<親ID>);
|
|
8
|
-
insert into 階層レベル select <自分のID>,レベル
|
|
7
|
+
insert into 階層レベル select <自分のID>,レベル,上位ID from 階層レベル where ID=<親ID>;
|
|
8
|
+
insert into 階層レベル select <自分のID>,max(レベル)+1,<自分のID> from 階層レベル where ID=<自分のID>;
|
|
9
9
|
```
|
|
10
10
|
質問の例の場合は以下のようなinsert文になります。
|
|
11
11
|
```SQL
|
|
12
|
-
insert into 階層レベル values(
|
|
12
|
+
insert into 階層レベル values(1,1,1);
|
|
13
|
-
insert into 階層レベル select 2,レベル
|
|
13
|
+
insert into 階層レベル select 2,レベル,上位ID from 階層レベル where ID=1;
|
|
14
|
-
insert into 階層レベル
|
|
14
|
+
insert into 階層レベル select 2,max(レベル)+1,2 from 階層レベル where ID=2;
|
|
15
|
-
insert into 階層レベル select 3,レベル
|
|
15
|
+
insert into 階層レベル select 3,レベル,上位ID from 階層レベル where ID=2;
|
|
16
|
-
insert into 階層レベル
|
|
16
|
+
insert into 階層レベル select 3,max(レベル)+1,3 from 階層レベル where ID=3;
|
|
17
|
-
insert into 階層レベル select 4,レベル
|
|
17
|
+
insert into 階層レベル select 4,レベル,上位ID from 階層レベル where ID=3;
|
|
18
|
-
insert into 階層レベル
|
|
18
|
+
insert into 階層レベル select 4,max(レベル)+1,4 from 階層レベル where ID=4;
|
|
19
|
-
insert into 階層レベル select 5,レベル
|
|
19
|
+
insert into 階層レベル select 5,レベル,上位ID from 階層レベル where ID=1;
|
|
20
|
-
insert into 階層レベル
|
|
20
|
+
insert into 階層レベル select 5,max(レベル)+1,5 from 階層レベル where ID=5;
|
|
21
|
-
insert into 階層レベル select 6,レベル
|
|
21
|
+
insert into 階層レベル select 6,レベル,上位ID from 階層レベル where ID=5;
|
|
22
|
-
insert into 階層レベル
|
|
22
|
+
insert into 階層レベル select 6,max(レベル)+1,6 from 階層レベル where ID=6;
|
|
23
|
-
insert into 階層レベル select 7,レベル
|
|
23
|
+
insert into 階層レベル select 7,レベル,上位ID from 階層レベル where ID=1;
|
|
24
|
+
insert into 階層レベル select 7,max(レベル)+1,7 from 階層レベル where ID=7;
|
|
24
25
|
```
|
|
25
26
|
これにより、以下のレコードになります。
|
|
26
27
|
```SQL
|
|
@@ -28,19 +29,51 @@
|
|
|
28
29
|
+------+--------+--------+
|
|
29
30
|
| ID | レベル | 上位ID |
|
|
30
31
|
+------+--------+--------+
|
|
32
|
+
| 1 | 1 | 1 |
|
|
31
|
-
| 2 |
|
|
33
|
+
| 2 | 1 | 1 |
|
|
32
|
-
|
|
|
34
|
+
| 2 | 2 | 2 |
|
|
33
35
|
| 3 | 1 | 1 |
|
|
36
|
+
| 3 | 2 | 2 |
|
|
37
|
+
| 3 | 3 | 3 |
|
|
38
|
+
| 4 | 1 | 1 |
|
|
39
|
+
| 4 | 2 | 2 |
|
|
34
|
-
| 4 |
|
|
40
|
+
| 4 | 3 | 3 |
|
|
35
|
-
| 4 |
|
|
41
|
+
| 4 | 4 | 4 |
|
|
36
|
-
| 4 | 2 | 1 |
|
|
37
|
-
| 5 |
|
|
42
|
+
| 5 | 1 | 1 |
|
|
38
|
-
|
|
|
43
|
+
| 5 | 2 | 5 |
|
|
39
44
|
| 6 | 1 | 1 |
|
|
45
|
+
| 6 | 2 | 5 |
|
|
46
|
+
| 6 | 3 | 6 |
|
|
40
|
-
| 7 |
|
|
47
|
+
| 7 | 1 | 1 |
|
|
48
|
+
| 7 | 2 | 7 |
|
|
41
49
|
+------+--------+--------+
|
|
42
50
|
```
|
|
43
51
|
|
|
52
|
+
フラグが1の名称の取得は以下のとおりです。
|
|
53
|
+
```SQL
|
|
54
|
+
select * from 階層レベル a inner join 階層構造のテーブル b on a.上位ID=b.ID where b.フラグ=1 and a.ID<>b.ID;
|
|
55
|
+
+------+--------+--------+------+------+------+--------+
|
|
56
|
+
| ID | レベル | 上位ID | ID | 名称 | 親ID | フラグ |
|
|
57
|
+
+------+--------+--------+------+------+------+--------+
|
|
58
|
+
| 4 | 3 | 3 | 3 | C | 2 | 1 |
|
|
59
|
+
| 6 | 2 | 5 | 5 | E | 1 | 1 |
|
|
60
|
+
+------+--------+--------+------+------+------+--------+
|
|
61
|
+
```
|
|
62
|
+
レベル2の名称の取得は以下のとおりです。
|
|
63
|
+
```SQL
|
|
64
|
+
select * from 階層レベル a inner join 階層構造のテーブル b on a.上位ID=b.ID where a.レベル=2;
|
|
65
|
+
+------+--------+--------+------+------+------+--------+
|
|
66
|
+
| ID | レベル | 上位ID | ID | 名称 | 親ID | フラグ |
|
|
67
|
+
+------+--------+--------+------+------+------+--------+
|
|
68
|
+
| 2 | 2 | 2 | 2 | B | 1 | 0 |
|
|
69
|
+
| 3 | 2 | 2 | 2 | B | 1 | 0 |
|
|
70
|
+
| 4 | 2 | 2 | 2 | B | 1 | 0 |
|
|
71
|
+
| 5 | 2 | 5 | 5 | E | 1 | 1 |
|
|
72
|
+
| 6 | 2 | 5 | 5 | E | 1 | 1 |
|
|
73
|
+
| 7 | 2 | 7 | 7 | G | 1 | 0 |
|
|
74
|
+
+------+--------+--------+------+------+------+--------+
|
|
75
|
+
```
|
|
76
|
+
|
|
44
77
|
なお、以下のデメリットがあります。
|
|
45
78
|
- 冗長なデータを持つ必要がある。
|
|
46
79
|
- 行数が階層の深さなどに依存して多くなる。
|