选择所有帖子及其针对特定用户的评论 [英] Select all post and its comments for a specific user

问题描述

我在这里有一个普遍的情况.我想为特定用户选择所有帖子及其评论.例如:

  1)邮政:1(PostId)，SomeTitle，SomeText2(PostId)，SomeTitle，SomeText注释:1，1(PostId)，CommentTitle，CommentText，UserId(1-来自另一个表)2，1(PostId)，CommentTitle，CommentText，UserId(1-来自另一个表)3、2(PostId)，CommentTitle，CommentText，UserId(2-来自另一个表)4，2(PostId)，CommentTitle，CommentText，UserId(2-来自另一个表) 

例如，我想通过用户ID选择一个结果集中的第一条帖子，以及选择另一结果集中的评论.简而言之，我想选择所有用户评论的帖子及其评论.谁能帮我?我当时想创建一个以userId和postId为参数的SP，但是我遇到了问题.

解决方案

这将选择所有帖子

  SELECT * FROM Post WHERE PostID IN(从评论WHERE UserID = 1中选择PostID)； 

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

  SELECT *从帖子AS P，注释AS C，其中C.PostID = P.PostID和C.UserID = 1组，由C.CommentId; 

(未经测试，但应该可以使用)

i have a common situation here. I want to select all posts and its comments for a specific user. For example:

 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)

I want to select, for example, first post in one result set and its comments in another result set by user id. In short, i would like to select all user commented posts and its comments. Can anyone help me? I was thinking to create a SP with userId and postId as parameters, but i had issues.

解决方案

This selects all posts

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

This select all posts and comments:

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

(Not tested, but should work)

这篇关于选择所有帖子及其针对特定用户的评论的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！