MySQL连接3个表并计算另一个

Question

我正在创建一个博客，其中包含一篇可以包含标签和评论的文章。我想尝试获取正确的 SQL 以获取一个查询。我有 4 个表:

article
+------------+-----------------+------------+
| article_id | title           | photo      |
+------------+-----------------+------------+
|          1 | This is a test  | image1.jpg |
|          2 | Another Article | image2.jpg |
+------------+-----------------+------------+


article_tag
+------------+--------+ 
| article_id | tag_id |
+------------+--------+
|          1 |      1 | 
|          1 |      2 | 
|          2 |      2 |
+------------+--------+

tags
+--------+------+ 
| tag_id | name |
+--------+------+ 
|      1 | tag1 |
|      2 | tag2 |
+--------+------+

comment
+------+---------+------------+
| name | message | article_id |
+------+---------+------------+
|    1 | hello   |          1 |
|    2 | a       |          2 |
+------+---------+------------+

我想得到这个:

+------------+----------------+------------+---------+----------+
| article_id | title          | photo      | tag_ids | comments |
+------------+----------------+------------+---------+----------+
|          1 | This is a test | image1.jpg | 1,2     |        1 |
+------------+----------------+------------+---------+----------+

这是我目前所拥有的:

SELECT a.article_id, a.title, a.photo, a.date, a.description_long, a.author, GROUP_CONCAT(tag_id) as `tag_ids`, COUNT(c.comment_id) as comments
FROM article as a
JOIN article_tag as at
ON a.article_id = at.article_id
LEFT JOIN comment as c
ON a.article_id = c.article_id
WHERE a.article_id = 1

但是我收到的评论是 2 条而不是 1 条？谢谢PS 如果有人知道我可以将 tag_ids 从 1,2 更改为 tag1,tag2 的方法，那就太棒了:-)

Answer 1

标签和评论是独立的。因此，如果您有三个标签和两个评论，您将得到 6 行的组合。

在 MySQL 中修复此查询的最简单方法是进行分组并在 select 中使用 distinct:

SELECT a.article_id, a.title, a.photo, a.date, a.description_long, a.author,
       GROUP_CONCAT(distinct tag_id) as `tag_ids`, COUNT(distinct c.comment_id) as comments
FROM article a JOIN
     article_tag as at
     ON a.article_id = at.article_id LEFT JOIN
     comment c
     ON a.article_id = c.article_id
WHERE a.article_id = 1
group by a.article_id

不过，我要说的是，修复查询的“正确”方法是修复连接。这使用 from 中的子查询:

from . . .
     (select article_id, group_concat(tag_id) as tags
      from tags
      group by article_id
     ) at
     . . .
     (select article_id, count(*) as numComments
      from comments
      group by article_id
     ) c
     . . .