mysql - mysql查询返回受WHERE子句限制的内连接数

Question

我有以下查询来尝试计算帖子发布后 30 分钟内发表的评论数。有两个表，posts 和 comments，它们是在 posts.id 和 comments 上进行 INNER JOIN 的.post_id 像这样:

SELECT id, count(post_id) as num_comm from posts
  INNER JOIN comments on id = post_id
  WHERE (UNIX_TIMESTAMP(posts.time_posted) - UNIX_TIMESTAMP(comments.time_posted)) < (30 * 60)
    AND comments.reply_to_id = 0
  GROUP BY id
  ORDER BY num_comm ASC;

我遇到的问题是，查询返回的是 num_comm 结果的总数，而不仅仅是原始帖子发布后 30 分钟内发表的评论的数量comments.reply_to_id 设置为 0。

如何更改查询以仅返回满足 WHERE 子句中条件的评论数量？

Answer 1

据推测，该帖子是在评论之前发布的。所以，你的值(value)永远是负数。尝试:

SELECT id, count(*) as num_comm 
FROM posts p INNER JOIN
     comments c
     ON p.id = c.post_id
WHERE (UNIX_TIMESTAMP(c.time_posted) - UNIX_TIMESTAMP(p.time_posted)) < (30 * 60) AND
      c.reply_to_id = 0
GROUP BY p.id
ORDER BY num_comm ASC;

我不太喜欢转换为 Unix 时间戳。我认为代码更清晰:

WHERE c.time_posted < p.time_posted + interval 30 minute AND
      c.reply_to_id = 0