回答編集履歴
1
調整
answer
CHANGED
|
@@ -10,4 +10,26 @@
|
|
|
10
10
|
left join my_tags as t4 on t2.tags_id=t4.ID and t4.tag_kind_id=2
|
|
11
11
|
group by ID
|
|
12
12
|
having genre_names is not null or author_name is not null
|
|
13
|
+
```
|
|
14
|
+
|
|
15
|
+
# my_tag_holders調整
|
|
16
|
+
```SQL
|
|
17
|
+
create table my_tag_holders2
|
|
18
|
+
(pid int primary key,cid int not null,gid int,aid int null,index(cid,gid,aid));
|
|
19
|
+
|
|
20
|
+
insert into my_tag_holders2 values
|
|
21
|
+
(1,1,3,1),
|
|
22
|
+
(2,1,3,2),
|
|
23
|
+
(3,3,3,null);
|
|
24
|
+
|
|
25
|
+
select t1.ID,
|
|
26
|
+
max(comment) as comment,
|
|
27
|
+
t3.tag_name as genre_name,
|
|
28
|
+
group_concat(t4.tag_name) as author_name
|
|
29
|
+
from my_comments as t1
|
|
30
|
+
inner join my_tag_holders2 as t2 on t1.ID=t2.cid
|
|
31
|
+
inner join my_tags as t3 on t2.gid=t3.ID
|
|
32
|
+
left join my_tags as t4 on t2.aid=t4.ID
|
|
33
|
+
group by ID,genre_name;
|
|
34
|
+
|
|
13
35
|
```
|