teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップMySQLに関する質問MySQLで、動的にレコードを参照できますか？

編集履歴

回答編集履歴

3

表示

2018/12/12 09:32

投稿

yambejp
yambejp

スコア117906

answer CHANGED
@@ -62,10 +62,27 @@
62
62
  insert into tbl values('A','X'),('A','Y'),('B','A'),('B','Z'),('C','B'),('C','N');
63
63
  ```
64
64
 
65
- - 更新データ
65
+ - 追加データ
66
66
  ```SQL
67
67
  insert into tbl values('D','A'),('D','B'),('D','X'),('D','O');
68
68
  ```
69
+ - tbl
70
+
71
+
72
+ |pname|cname|
73
+ |:--:|:--:|
74
+ |A|X|
75
+ |A|Y|
76
+ |B|A|
77
+ |B|Z|
78
+ |C|B|
79
+ |C|N|
80
+ |D|A|
81
+ |D|B|
82
+ |D|X|
83
+ |D|O|
84
+
85
+
69
86
  - ツリーテーブル作成
70
87
 
71
88
  ```SQL
@@ -114,4 +131,96 @@
114
131
 
115
132
  ```SQL
116
133
  call set_tree_table;
134
+ ```
135
+
136
+ - treeテーブル
137
+
138
+ |id|pid|cname|level|l|r|
139
+ |--:|--:|:--:|--:|--:|--:|
140
+ |1|NULL|A|1|1|6|
141
+ |2|NULL|B|1|7|16|
142
+ |3|NULL|C|1|17|30|
143
+ |4|NULL|D|1|31|52|
144
+ |8|1|X|2|2|3|
145
+ |9|1|Y|2|4|5|
146
+ |10|2|A|2|8|13|
147
+ |11|2|Z|2|14|15|
148
+ |12|3|B|2|18|27|
149
+ |13|3|N|2|28|29|
150
+ |14|4|A|2|32|37|
151
+ |15|4|B|2|38|47|
152
+ |16|4|X|2|48|49|
153
+ |17|4|O|2|50|51|
154
+ |23|10|X|3|9|10|
155
+ |24|14|X|3|33|34|
156
+ |25|10|Y|3|11|12|
157
+ |26|14|Y|3|35|36|
158
+ |27|12|A|3|19|24|
159
+ |28|15|A|3|39|44|
160
+ |29|12|Z|3|25|26|
161
+ |30|15|Z|3|45|46|
162
+ |38|27|X|4|20|21|
163
+ |39|28|X|4|40|41|
164
+ |40|27|Y|4|22|23|
165
+ |41|28|Y|4|42|43|
166
+
167
+ # 具体的なデータ
168
+
169
+ - 全構成要素を表示
170
+
171
+ ```SQL
172
+ select t1.cname,group_concat(distinct t2.cname) as all_child from tree as t1
173
+ inner join tree as t2 on t2.l > t1.l and t2.l < t1.r
174
+ where t1.pid is null
175
+ group by cname;
176
+ ```
177
+
178
+
179
+ - 結果
180
+
181
+ |cname|all_child|
182
+ |:--|:--|
183
+ |A|Y,X|
184
+ |B|X,Y,Z,A|
185
+ |C|B,A,Z,X,Y,N|
186
+ |D|Y,A,X,B,O,Z|
187
+
188
+
189
+ - 階層状況を視覚化
190
+
191
+ ```SQL
192
+ select concat(repeat('__', level -1 ) , cname ) AS cname
193
+ FROM tree
194
+ order by l;
195
+ ```
196
+
197
+ - 結果
198
+ ```
199
+ A
200
+ __X
201
+ __Y
202
+ B
203
+ __A
204
+ ____X
205
+ ____Y
206
+ __Z
207
+ C
208
+ __B
209
+ ____A
210
+ ______X
211
+ ______Y
212
+ ____Z
213
+ __N
214
+ D
215
+ __A
216
+ ____X
217
+ ____Y
218
+ __B
219
+ ____A
220
+ ______X
221
+ ______Y
222
+ ____Z
223
+ __X
224
+ __O
225
+
117
226
  ```

2

調整

2018/12/12 09:32

投稿

yambejp
yambejp

スコア117906

answer CHANGED
@@ -60,6 +60,8 @@
60
60
  ```SQL
61
61
  create table tbl(pname varchar(10),cname varchar(10));
62
62
  insert into tbl values('A','X'),('A','Y'),('B','A'),('B','Z'),('C','B'),('C','N');
63
+ ```
64
+
63
65
  - 更新データ
64
66
  ```SQL
65
67
  insert into tbl values('D','A'),('D','B'),('D','X'),('D','O');

1

追記

2018/12/12 08:02

投稿

yambejp
yambejp

スコア117906

answer CHANGED
@@ -52,4 +52,64 @@
52
52
  left join tbl t9 on t8.cid=t9.pid
53
53
  group by pid
54
54
  ```
55
- 上記もDのように使用部品の競合があるとユニークに拾えないので別処理が必要になります
55
+ 上記もDのように使用部品の競合があるとユニークに拾えないので別処理が必要になります
56
+
57
+ # procedureで処理
58
+ 久しぶりに真面目に書いてみました
59
+ - 元データ
60
+ ```SQL
61
+ create table tbl(pname varchar(10),cname varchar(10));
62
+ insert into tbl values('A','X'),('A','Y'),('B','A'),('B','Z'),('C','B'),('C','N');
63
+ - 更新データ
64
+ ```SQL
65
+ insert into tbl values('D','A'),('D','B'),('D','X'),('D','O');
66
+ ```
67
+ - ツリーテーブル作成
68
+
69
+ ```SQL
70
+ create table tree (id int NOT NULL primary key auto_increment,pid INT null,cname varchar(64),level int NOT NULL default 0,l int not null default 0,r int not null default 0);
71
+ ```
72
+
73
+ - procedure作成
74
+ ```SQL
75
+ drop procedure if exists set_tree_table;
76
+ delimiter //
77
+ create procedure set_tree_table()
78
+ begin
79
+ declare count int default 0;
80
+ declare count_stop int default 5;
81
+ declare lastid int default 0;
82
+ declare done int default 0;
83
+ declare a int default 0;
84
+ declare cur cursor for
85
+ select id from tree where level=0 order by pid asc,id desc;
86
+ truncate tree;
87
+ insert into tree(cname,level)
88
+ select distinct pname,1 from tbl;
89
+ insert into tree(pid,cname,level)
90
+ select t2.id,t1.cname,2 from tbl as t1
91
+ inner join tree as t2 on t1.pname=t2.cname;
92
+ while count<count_stop and done=0 do
93
+ insert into tree(pid,cname,level)
94
+ select distinct t1.id,t3.cname,(count+3) from tree as t1
95
+ inner join tree as t2 on t1.cname=t2.cname and t1.level=(count+2) and t2.level=(count+1)
96
+ inner join tree as t3 on t2.id=t3.pid;
97
+ select @lastid:=LAST_INSERT_ID();
98
+ set count=count+1;
99
+ if lastid=@lastid then
100
+ set done=1;
101
+ else
102
+ set lastid=@lastid;
103
+ end if;
104
+ end while;
105
+ end
106
+ //
107
+ delimiter ;
108
+ ```
109
+
110
+ - procedure実行
111
+ tblのデータを追加、削除、更新したら都度procudureを実行します
112
+
113
+ ```SQL
114
+ call set_tree_table;
115
+ ```