mysql - 选择特定用户的所有帖子及其评论

Question

我这里有一个常见的情况。我想选择特定用户的所有帖子及其评论。例如:

 1)

   Post:
     1(PostId), SomeTitle, SomeText
     2(PostId), SomeTitle, SomeText
   Comments:
     1, 1(PostId), CommentTitle, CommentText, UserId(1 - from another table)
     2, 1(PostId), CommentTitle, CommentText, UserId(1 - from another table)
     3, 2(PostId), CommentTitle, CommentText, UserId(2 - from another table)
     4, 2(PostId), CommentTitle, CommentText, UserId(2 - from another table)

例如，我想按用户 ID 选择一个结果集中的第一篇文章及其在另一个结果集中的评论。简而言之，我想选择所有用户评论的帖子及其评论。谁能帮我？我正在考虑创建一个以 userId 和 postId 作为参数的 SP，但我遇到了问题。

Answer 1

这会选择所有帖子

SELECT * FROM Post WHERE PostID IN (SELECT PostID FROM Comments WHERE UserID = 1);

这将选择所有帖子和评论:

SELECT * FROM Post AS P, Comments AS C WHERE C.PostID = P.PostID AND C.UserID = 1 group by C.CommentId;

(未经测试，但应该有效)