获取我用新评论评论过的帖子

Question

我为此设置了一个典型的MySQL数据库：post表、user表、comment表。

查询"获取自上次评论以来有新评论的帖子"之类的内容的最佳方式是什么？我很难理解如何将其他用户的comment_timestamp大于我的comment_timestamp条件合并到下面的查询中：

我大致有

SELECT * FROM posts 
WHERE posts.id IN 
( SELECT DISTINCT posts.id FROM posts,users,comments WHERE comments.post_id = post.id AND comments.user_id = $current_user_id ) AND .. something to filter only comments where timestamp is greater than mine

我意识到我可以循环通过一个集合并在MySQL之外进行过滤，但如果可能的话，我更愿意在MySQL中完成所有操作。我知道加入可能有更好的方法，但我不知道如何在加入的两个部分中移动"我"最后一条评论的comment_timestamp

user table
-----
id
posts table
------
id
user_id
comments table
-------
id
user_id
post_id
comment_timestamp

Answer 1

SELECT DISTINCT p.*
FROM posts p
INNER JOIN comments c
ON c.post_id = p.id
WHERE c.comment_timestamp > (
   SELECT MAX(c2.comment_timestamp)
   FROM comments c2
   WHERE c2.post_id = c.post_id
   AND c2.user_id = $current_user_id
) 

为每个具有由不同用户创建的评论的帖子选择帖子数据，该评论的时间戳值大于当前用户最近发表的评论。包含Distinct只获取每条帖子一次，因为一条帖子可能在当前用户的最新帖子之后有多条评论。

SQL Fiddle。fiddle假设当前用户的ID为100，并提供以下示例案例：

• 一个帖子，其中最近的评论是当前用户的，其他用户以前也有评论
• 当前用户发表评论，其他用户随后发表评论的帖子
• 当前用户未发表评论但其他用户发表了评论的帖子
• 没有评论的帖子
• 仅包含当前用户评论的帖子