在mysql中对左联接使用限制 [英] Using a limit on a left join in mysql

问题描述

以下查询选择所有帖子以及每个帖子的所有者，属于每个帖子的所有评论以及每个评论的所有者.

The following query selects all posts and each post's owner, all of the comments that belong to each post, and the owner of each comment.

我每个帖子只需要检索5条评论.我重新编写了查询，但是出现错误每个派生表必须具有它自己的别名".

I need to only retrieve 5 comments per post. I rewrote the query, but I get an error of "each derived table must have it's own alias".

SELECT posts.id AS postId, posts.body, users.id AS userId, users.displayname, comments.id AS commentId, comments.text, commenters.id, commenters.displayname
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN comments ON posts.id = comments.postid
    JOIN users AS commenters ON comments.userId = commenters.id
ORDER BY posts.createdAt

新查询:

SELECT posts.id AS postId, posts.body, users.id AS userId, users.displayname
FROM posts
JOIN users ON posts.owneruserid = users.id
LEFT JOIN (
    SELECT comments.id AS commentId, comments.text AS commentText, commenters.id AS commenterId, commenters.displayname AS commenterDisplayName
    FROM comments
    JOIN users AS commenters ON comments.userid = commenters.id
    LIMIT 0,5
        ) AS comments ON comments.postid = posts.id
ORDER BY posts.createdAt

更新该查询现在可以使用，但不会产生所需的输出.我想输出10个帖子，每个帖子有5条评论.该限制条款仅适用于遇到的第一篇文章的评论.

UPDATE The query now works, but it does not produce the desired output. I want to output 10 posts, with 5 comments for each post. This limit clause will only apply for the comments of the first post encountered.

推荐答案

从编辑和评论反馈中，这是我认为您正在寻找的查询...最内在的预查询将获得该帖子以及发起该帖子的人，评论以及发表评论的人.此内部查询还与最近的评论"一起在每个postID的顶部进行了预排序.使用结果，我将加入sql变量(@variables)，以使@varRow在每次添加新注释时增加，并在每次帖子ID更改时重置回1(因此，通过帖子ID FIRST发出的内部PreQuery顺序).最后，使用HAVING子句使注释的@varRow计数< 6个将获得每个帖子的MOST 5.

From the edits and comment feedback, here's the query I think you are looking for... The inner most will prequery gets the posts and who initiated the post, comments and who posted the comments. This inner query is also pre-sorted with the MOST RECENT COMMENTS to the top per postID. Using the result of that, I'm joining to the sql variables (@variables) to get the @varRow increased every time a new comment and reset back to 1 each time a post ID changes (hence the inner PreQuery orders by post ID FIRST). Finally, using the HAVING clause to have the comment's @varRow count < 6 will get at MOST 5 of each post.

如果您想限制要检索的帖子，我将在生成"PreQuery"的最内层应用WHERE子句(例如日期/时间，如果可用).

If you want to limit what posts you are trying to retrieve, I would apply a WHERE clause (such as date/time if available) at the INNER most that generates the "PreQuery".

select straight_join
      PreQuery.*,
      @varRow := if( @LastPost = PreQuery.PostID, @varRow +1, 1 ) CommentRow,
      @LastPost := PreQuery.PostID PostID2
   from
      ( select
              posts.id PostID,
              posts.body,
              posts.CreatedAt,
              u1.id UserID,
              u1.DisplayName NameOfPoster,
              c.id,
              c.userid CommentUserID,
              c.text CommentText,
              u2.DisplayName CommentUserName
           from
              posts
                 join users u1
                    on posts.ownerUserID = u1.id

                 LEFT JOIN comments c
                    on posts.id = c.PostID

                    join users u2
                       on c.userid = u2.id 
            where
                  posts.id = TheOneParentIDYouWant
               OR posts.parentid = TheOneParentIDYouWant
            order by
               posts.ID,
               c.id desc ) PreQuery,

      (select @varRow := 0, @LastPost = 0 ) SQLVars

   having
      CommentRow < 6   

   order by
      PreQuery.postid,
      CommentRow

-编辑-每个评论 我认为与注释关联的父帖子"的意思是因为它们直接具有帖子ID.由于最内层的查询会全面连接所有元素/表，因此所有这些元素/表都将随之而来...

--- EDIT --- per comment I THINK what you mean by which "Parent Post" the comments are associated with is because they have the post ID directly. Since the inner-most query does a join of all elements / tables across the board, all are coming along for the ride...

Post -> User (to get posting user name )
Post -> Comment (on Common Post ID -- left joined)
        Comment -> User ( to get commenting user name)

完成所有操作并按通用的帖子ID进行排序，最新的评论按顶部排序后，我便对所有返回的行应用@vars. HAVING子句会删除序列，顺序是您要查找的5之外的任何注释.

Once THAT is all done and sorted by common Post ID and most recent comment sorted to the top, I then apply the @vars against ALL returned rows. The HAVING clause will strip out any comment where it's sequence is BEYOND the 5 you were looking for.

这篇关于在mysql中对左联接使用限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！