mysql - LEFT JOIN 显示重复匹配项

Question

当我左连接这两个表“posts”和“comments”时:

SELECT *
FROM  `posts` 

+----+-----------+
| id | post_text |
+----+-----------+
|  1 | test0     |
|  2 | test1     |
|  3 | test2     |
|  4 | test3     |
|  5 | test4     |
|  6 | test5     |
|  7 | test6     |
|  8 | test7     |
|  9 | test8     |
| 10 | test9     |
| 11 | test10    |
+----+-----------+

SELECT *
FROM  `comments`

+----+------------------------------------------+---------+
| id |                 comment                  | post_id |
+----+------------------------------------------+---------+
|  1 | hello there                              |       4 |
|  2 | this is another comment on the same post |       4 |
|  3 | this is a comment on a different post    |       7 |
+----+------------------------------------------+---------+

我得到以下信息:

SELECT posts.id, post_id, COUNT( * ) 
FROM posts
LEFT JOIN posts ON posts.id = comments.post_id
GROUP BY posts.id

+----------+---------+----------+
| posts.id | post_id | COUNT(*) |
+----------+---------+----------+
| 4        | 4       | 2        |
| 7        | 7       | 1        |
| ...      | ...     | ...      |
| ...      | ...     | ...      |
+----------+---------+----------+

我想做的是，如果 COUNT(*) 大于 1(意味着帖子中有多个评论)，则显示所有匹配的记录，而不是将它们分组到一行中，所以它是像这样:

+----------+---------+----------+
| posts.id | post_id | COUNT(*) |
+----------+---------+----------+
| 4        | 4       | 1        |
| 4        | 4       | 1        |
| 7        | 7       | 1        |
| ...      | ...     | ...      |
| ...      | ...     | ...      |
+----------+---------+----------+

Answer 1

如果您放下count 和group by，您可以选择所有记录。为了仍然显示每个帖子的评论数，您可以使用相关子查询。

另外，我假设你真的不想同时显示 posts.id 和 post_id 因为它们是相同的值，所以下面的查询应该是更像你的意图:

select posts.id as post_id, comments.id as comment_id, (select count(1) from comments where post_id = posts.id) as comment_count
from posts
left join comments on posts.id = comments.post_id

这是一个 SQL Fiddle demo .