回答編集履歴
2
インデックス
test
CHANGED
|
@@ -4,19 +4,17 @@
|
|
|
4
4
|
|
|
5
5
|
```SQL
|
|
6
6
|
|
|
7
|
-
create table users(id int primary key,name varchar(10));
|
|
7
|
+
create table users(id int primary key,name varchar(10),index(name));
|
|
8
8
|
|
|
9
9
|
insert into users values(1,'userA'),(2,'userB'),(3,'userC'),(4,'userD'),(5,'userE');
|
|
10
10
|
|
|
11
|
-
create table posts(id int primary key,user_id int);
|
|
11
|
+
create table posts(id int primary key,user_id int,index(user_id));
|
|
12
12
|
|
|
13
13
|
insert into posts values(1,1),(2,2),(3,3),(4,4);
|
|
14
14
|
|
|
15
|
-
create table comments(id int primary key,post_id int,body varchar(30));
|
|
15
|
+
create table comments(id int primary key,post_id int,body varchar(30),index(post_id,body));
|
|
16
16
|
|
|
17
17
|
insert into comments values(1,1,'foo'),(2,1,'bar'),(3,2,'foo'),(4,2,'foo'),(5,3,'foo');
|
|
18
|
-
|
|
19
|
-
|
|
20
18
|
|
|
21
19
|
```
|
|
22
20
|
|
1
修正
test
CHANGED
|
@@ -24,7 +24,7 @@
|
|
|
24
24
|
|
|
25
25
|
※userDはpostsがあるけどpostsがcommentsを持っていない
|
|
26
26
|
|
|
27
|
-
※user
|
|
27
|
+
※userAはcommentsがfooを含んでいないものを持っている
|
|
28
28
|
|
|
29
29
|
|
|
30
30
|
|
|
@@ -36,14 +36,18 @@
|
|
|
36
36
|
|
|
37
37
|
inner join posts as t2 on t1.id=t2.user_id
|
|
38
38
|
|
|
39
|
-
and
|
|
39
|
+
and exists(
|
|
40
40
|
|
|
41
|
-
select 1 from comments
|
|
41
|
+
select 1 from comments
|
|
42
42
|
|
|
43
|
-
group by id
|
|
43
|
+
group by post_id
|
|
44
44
|
|
|
45
|
-
having count(*)
|
|
45
|
+
having sum((body regexp 'foo'))=count(*)
|
|
46
46
|
|
|
47
|
+
and post_id=t2.id
|
|
48
|
+
|
|
47
|
-
)
|
|
49
|
+
);
|
|
48
50
|
|
|
49
51
|
```
|
|
52
|
+
|
|
53
|
+
※ロジックがおかしかったので修正しました
|