回答編集履歴
1
tuiki
test
CHANGED
|
@@ -39,3 +39,83 @@
|
|
|
39
39
|
</form>
|
|
40
40
|
|
|
41
41
|
```
|
|
42
|
+
|
|
43
|
+
|
|
44
|
+
|
|
45
|
+
# mysql側の処理
|
|
46
|
+
|
|
47
|
+
データの持ち方はこうしてください
|
|
48
|
+
|
|
49
|
+
```SQL
|
|
50
|
+
|
|
51
|
+
create table user (uid int primary key ,uname varchar(30));
|
|
52
|
+
|
|
53
|
+
insert into user values(1,'佐藤'),(2,'鈴木'),(3,'田中'),(4,'中村');
|
|
54
|
+
|
|
55
|
+
|
|
56
|
+
|
|
57
|
+
create table tokucho(tid int primary key,tname varchar(30));
|
|
58
|
+
|
|
59
|
+
insert into tokucho values(1,'やさしい'),(2,'楽しい'),(3,'話が面白い'),(4,'スポーツ好き'),(5,'料理好き'),(6,'オンオフはっきり');
|
|
60
|
+
|
|
61
|
+
|
|
62
|
+
|
|
63
|
+
create table user_tokucho (utid int primary key,uid int not null,tid int not null,unique(uid,tid));
|
|
64
|
+
|
|
65
|
+
insert into user_tokucho values
|
|
66
|
+
|
|
67
|
+
(1,1,1),(2,1,2),(3,2,2),(4,2,3),(5,2,4),(6,2,5),(7,2,6),(8,3,1),(9,4,2);
|
|
68
|
+
|
|
69
|
+
```
|
|
70
|
+
|
|
71
|
+
そうすると個人ごとの特徴の一覧がこうなります
|
|
72
|
+
|
|
73
|
+
```SQL
|
|
74
|
+
|
|
75
|
+
select t1.uname,group_concat(t3.tname) as tnames
|
|
76
|
+
|
|
77
|
+
from user as t1
|
|
78
|
+
|
|
79
|
+
inner join user_tokucho as t2 on t1.uid=t2.uid
|
|
80
|
+
|
|
81
|
+
inner join tokucho as t3 on t2.tid=t3.tid
|
|
82
|
+
|
|
83
|
+
group by uname
|
|
84
|
+
|
|
85
|
+
```
|
|
86
|
+
|
|
87
|
+
特徴が「やさしい」だけなのでこれを絞り込みます
|
|
88
|
+
|
|
89
|
+
```SQL
|
|
90
|
+
|
|
91
|
+
select t1.uname,group_concat(t3.tname) as tnames
|
|
92
|
+
|
|
93
|
+
from user as t1
|
|
94
|
+
|
|
95
|
+
inner join user_tokucho as t2 on t1.uid=t2.uid
|
|
96
|
+
|
|
97
|
+
inner join tokucho as t3 on t2.tid=t3.tid
|
|
98
|
+
|
|
99
|
+
group by uname
|
|
100
|
+
|
|
101
|
+
having tnames='やさしい'
|
|
102
|
+
|
|
103
|
+
```
|
|
104
|
+
|
|
105
|
+
ちゃんとやるならこう
|
|
106
|
+
|
|
107
|
+
```SQL
|
|
108
|
+
|
|
109
|
+
select * from user as t1 where exists(
|
|
110
|
+
|
|
111
|
+
select 1 from user_tokucho as t2
|
|
112
|
+
|
|
113
|
+
group by uid
|
|
114
|
+
|
|
115
|
+
having count(*)=1
|
|
116
|
+
|
|
117
|
+
and sum(tid=(select tid from tokucho where tname='やさしい'))=1
|
|
118
|
+
|
|
119
|
+
and uid=t1.uid)
|
|
120
|
+
|
|
121
|
+
```
|