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

編集履歴

回答編集履歴

3

表示

2018/12/12 09:32

投稿

yambejp
yambejp

スコア114878

test CHANGED
@@ -126,7 +126,7 @@
126
126
 
127
127
 
128
128
 
129
- - 更新データ
129
+ - 追加データ
130
130
 
131
131
  ```SQL
132
132
 
@@ -134,6 +134,40 @@
134
134
 
135
135
  ```
136
136
 
137
+ - tbl
138
+
139
+
140
+
141
+
142
+
143
+ |pname|cname|
144
+
145
+ |:--:|:--:|
146
+
147
+ |A|X|
148
+
149
+ |A|Y|
150
+
151
+ |B|A|
152
+
153
+ |B|Z|
154
+
155
+ |C|B|
156
+
157
+ |C|N|
158
+
159
+ |D|A|
160
+
161
+ |D|B|
162
+
163
+ |D|X|
164
+
165
+ |D|O|
166
+
167
+
168
+
169
+
170
+
137
171
  - ツリーテーブル作成
138
172
 
139
173
 
@@ -231,3 +265,187 @@
231
265
  call set_tree_table;
232
266
 
233
267
  ```
268
+
269
+
270
+
271
+ - treeテーブル
272
+
273
+
274
+
275
+ |id|pid|cname|level|l|r|
276
+
277
+ |--:|--:|:--:|--:|--:|--:|
278
+
279
+ |1|NULL|A|1|1|6|
280
+
281
+ |2|NULL|B|1|7|16|
282
+
283
+ |3|NULL|C|1|17|30|
284
+
285
+ |4|NULL|D|1|31|52|
286
+
287
+ |8|1|X|2|2|3|
288
+
289
+ |9|1|Y|2|4|5|
290
+
291
+ |10|2|A|2|8|13|
292
+
293
+ |11|2|Z|2|14|15|
294
+
295
+ |12|3|B|2|18|27|
296
+
297
+ |13|3|N|2|28|29|
298
+
299
+ |14|4|A|2|32|37|
300
+
301
+ |15|4|B|2|38|47|
302
+
303
+ |16|4|X|2|48|49|
304
+
305
+ |17|4|O|2|50|51|
306
+
307
+ |23|10|X|3|9|10|
308
+
309
+ |24|14|X|3|33|34|
310
+
311
+ |25|10|Y|3|11|12|
312
+
313
+ |26|14|Y|3|35|36|
314
+
315
+ |27|12|A|3|19|24|
316
+
317
+ |28|15|A|3|39|44|
318
+
319
+ |29|12|Z|3|25|26|
320
+
321
+ |30|15|Z|3|45|46|
322
+
323
+ |38|27|X|4|20|21|
324
+
325
+ |39|28|X|4|40|41|
326
+
327
+ |40|27|Y|4|22|23|
328
+
329
+ |41|28|Y|4|42|43|
330
+
331
+
332
+
333
+ # 具体的なデータ
334
+
335
+
336
+
337
+ - 全構成要素を表示
338
+
339
+
340
+
341
+ ```SQL
342
+
343
+ select t1.cname,group_concat(distinct t2.cname) as all_child from tree as t1
344
+
345
+ inner join tree as t2 on t2.l > t1.l and t2.l < t1.r
346
+
347
+ where t1.pid is null
348
+
349
+ group by cname;
350
+
351
+ ```
352
+
353
+
354
+
355
+
356
+
357
+ - 結果
358
+
359
+
360
+
361
+ |cname|all_child|
362
+
363
+ |:--|:--|
364
+
365
+ |A|Y,X|
366
+
367
+ |B|X,Y,Z,A|
368
+
369
+ |C|B,A,Z,X,Y,N|
370
+
371
+ |D|Y,A,X,B,O,Z|
372
+
373
+
374
+
375
+
376
+
377
+ - 階層状況を視覚化
378
+
379
+
380
+
381
+ ```SQL
382
+
383
+ select concat(repeat('__', level -1 ) , cname ) AS cname
384
+
385
+ FROM tree
386
+
387
+ order by l;
388
+
389
+ ```
390
+
391
+
392
+
393
+ - 結果
394
+
395
+ ```
396
+
397
+ A
398
+
399
+ __X
400
+
401
+ __Y
402
+
403
+ B
404
+
405
+ __A
406
+
407
+ ____X
408
+
409
+ ____Y
410
+
411
+ __Z
412
+
413
+ C
414
+
415
+ __B
416
+
417
+ ____A
418
+
419
+ ______X
420
+
421
+ ______Y
422
+
423
+ ____Z
424
+
425
+ __N
426
+
427
+ D
428
+
429
+ __A
430
+
431
+ ____X
432
+
433
+ ____Y
434
+
435
+ __B
436
+
437
+ ____A
438
+
439
+ ______X
440
+
441
+ ______Y
442
+
443
+ ____Z
444
+
445
+ __X
446
+
447
+ __O
448
+
449
+
450
+
451
+ ```

2

調整

2018/12/12 09:32

投稿

yambejp
yambejp

スコア114878

test CHANGED
@@ -122,6 +122,10 @@
122
122
 
123
123
  insert into tbl values('A','X'),('A','Y'),('B','A'),('B','Z'),('C','B'),('C','N');
124
124
 
125
+ ```
126
+
127
+
128
+
125
129
  - 更新データ
126
130
 
127
131
  ```SQL

1

追記

2018/12/12 08:02

投稿

yambejp
yambejp

スコア114878

test CHANGED
@@ -107,3 +107,123 @@
107
107
  ```
108
108
 
109
109
  上記もDのように使用部品の競合があるとユニークに拾えないので別処理が必要になります
110
+
111
+
112
+
113
+ # procedureで処理
114
+
115
+ 久しぶりに真面目に書いてみました
116
+
117
+ - 元データ
118
+
119
+ ```SQL
120
+
121
+ create table tbl(pname varchar(10),cname varchar(10));
122
+
123
+ insert into tbl values('A','X'),('A','Y'),('B','A'),('B','Z'),('C','B'),('C','N');
124
+
125
+ - 更新データ
126
+
127
+ ```SQL
128
+
129
+ insert into tbl values('D','A'),('D','B'),('D','X'),('D','O');
130
+
131
+ ```
132
+
133
+ - ツリーテーブル作成
134
+
135
+
136
+
137
+ ```SQL
138
+
139
+ 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);
140
+
141
+ ```
142
+
143
+
144
+
145
+ - procedure作成
146
+
147
+ ```SQL
148
+
149
+ drop procedure if exists set_tree_table;
150
+
151
+ delimiter //
152
+
153
+ create procedure set_tree_table()
154
+
155
+ begin
156
+
157
+ declare count int default 0;
158
+
159
+ declare count_stop int default 5;
160
+
161
+ declare lastid int default 0;
162
+
163
+ declare done int default 0;
164
+
165
+ declare a int default 0;
166
+
167
+ declare cur cursor for
168
+
169
+ select id from tree where level=0 order by pid asc,id desc;
170
+
171
+ truncate tree;
172
+
173
+ insert into tree(cname,level)
174
+
175
+ select distinct pname,1 from tbl;
176
+
177
+ insert into tree(pid,cname,level)
178
+
179
+ select t2.id,t1.cname,2 from tbl as t1
180
+
181
+ inner join tree as t2 on t1.pname=t2.cname;
182
+
183
+ while count<count_stop and done=0 do
184
+
185
+ insert into tree(pid,cname,level)
186
+
187
+ select distinct t1.id,t3.cname,(count+3) from tree as t1
188
+
189
+ inner join tree as t2 on t1.cname=t2.cname and t1.level=(count+2) and t2.level=(count+1)
190
+
191
+ inner join tree as t3 on t2.id=t3.pid;
192
+
193
+ select @lastid:=LAST_INSERT_ID();
194
+
195
+ set count=count+1;
196
+
197
+ if lastid=@lastid then
198
+
199
+ set done=1;
200
+
201
+ else
202
+
203
+ set lastid=@lastid;
204
+
205
+ end if;
206
+
207
+ end while;
208
+
209
+ end
210
+
211
+ //
212
+
213
+ delimiter ;
214
+
215
+ ```
216
+
217
+
218
+
219
+ - procedure実行
220
+
221
+ tblのデータを追加、削除、更新したら都度procudureを実行します
222
+
223
+
224
+
225
+ ```SQL
226
+
227
+ call set_tree_table;
228
+
229
+ ```