回答編集履歴
2
調整
test
CHANGED
|
@@ -1,28 +1,45 @@
|
|
|
1
1
|
認識が違ったらすみません。2個のtag同士を限定的に関連性をもたせるならこんな感じとか
|
|
2
2
|
```SQL
|
|
3
3
|
create table tbl_tag_relations(
|
|
4
|
-
relation_id int,
|
|
4
|
+
relation_id int not null,
|
|
5
|
-
num enum('1','2'),
|
|
5
|
+
num enum('1','2') not null,
|
|
6
6
|
tag_id int,
|
|
7
7
|
primary key(relation_id,num),
|
|
8
8
|
unique(relation_id,tag_id));
|
|
9
9
|
|
|
10
10
|
insert into tbl_tag_relations values
|
|
11
|
-
(1,'1',1),(1,'2',3),(2,'1',2),(2,'2',3),(3,'1',3),(3,2,4);
|
|
11
|
+
(1,'1',1),(1,'2',3),(2,'1',2),(2,'2',3),(3,'1',3),(3,'2',4);
|
|
12
12
|
```
|
|
13
|
+
※テーブルのカラム設定とデータ投入を調整しました
|
|
14
|
+
|
|
15
|
+
# テスト
|
|
16
|
+
以下でテストしてみて
|
|
17
|
+
```SQL
|
|
18
|
+
select * from tbl_tag_relations as t1
|
|
19
|
+
inner join tbl_tag_relations as t2
|
|
20
|
+
on t1.relation_id=t2.relation_id
|
|
21
|
+
and t1.num<>t2.num
|
|
22
|
+
```
|
|
23
|
+
こうなりますか？
|
|
24
|
+
|relation_id|num|tag_id|relation_id|num|tag_id|
|
|
25
|
+
|--:|--:|--:|--:|--:|--:|
|
|
26
|
+
|1|1|1|1|2|3|
|
|
27
|
+
|1|2|3|1|1|1|
|
|
28
|
+
|2|1|2|2|2|3|
|
|
29
|
+
|2|2|3|2|1|2|
|
|
30
|
+
|3|1|3|3|2|4|
|
|
31
|
+
|3|2|4|3|1|3|
|
|
32
|
+
|
|
13
|
-
#
|
|
33
|
+
# 検索
|
|
14
|
-
上
|
|
34
|
+
その上で、1と関連するtag_idを検索
|
|
15
35
|
|
|
16
36
|
```SQL
|
|
17
37
|
select t1.tag_id from tbl_tag_relations as t1
|
|
18
38
|
inner join tbl_tag_relations as t2
|
|
19
39
|
on t1.relation_id=t2.relation_id
|
|
20
40
|
and t1.num<>t2.num
|
|
21
|
-
and t2.tag_id=1;
|
|
41
|
+
and t2.tag_id=1;
|
|
22
|
-
|
|
23
|
-
select t1.tag_id from tbl_tag_relations as t1
|
|
24
|
-
inner join tbl_tag_relations as t2
|
|
25
|
-
on t1.relation_id=t2.relation_id
|
|
26
|
-
and t1.num<>t2.num
|
|
27
|
-
and t2.tag_id=3; /* 1,2,4 */
|
|
28
42
|
```
|
|
43
|
+
|tag_id|
|
|
44
|
+
|--:|
|
|
45
|
+
|3|
|
1
tuiki
test
CHANGED
|
@@ -9,4 +9,20 @@
|
|
|
9
9
|
|
|
10
10
|
insert into tbl_tag_relations values
|
|
11
11
|
(1,'1',1),(1,'2',3),(2,'1',2),(2,'2',3),(3,'1',3),(3,2,4);
|
|
12
|
+
```
|
|
13
|
+
# 追記
|
|
14
|
+
上記より1と関連するtag_id、3と関連するtag_id
|
|
12
15
|
|
|
16
|
+
```SQL
|
|
17
|
+
select t1.tag_id from tbl_tag_relations as t1
|
|
18
|
+
inner join tbl_tag_relations as t2
|
|
19
|
+
on t1.relation_id=t2.relation_id
|
|
20
|
+
and t1.num<>t2.num
|
|
21
|
+
and t2.tag_id=1; /* 3 */
|
|
22
|
+
|
|
23
|
+
select t1.tag_id from tbl_tag_relations as t1
|
|
24
|
+
inner join tbl_tag_relations as t2
|
|
25
|
+
on t1.relation_id=t2.relation_id
|
|
26
|
+
and t1.num<>t2.num
|
|
27
|
+
and t2.tag_id=3; /* 1,2,4 */
|
|
28
|
+
```
|