在 mysql 中使用左连接限制

发布于 11-13 03:46 字数 1073 浏览 4 评论 0原文

以下查询选择所有帖子和每个帖子的所有者、属于每个帖子的所有评论以及每个评论的所有者。

我只需要检索每个帖子 5 条评论。我重写了查询,但收到错误“每个派生表必须有自己的别名”。

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 条评论。此限制条款仅适用于遇到的第一个帖子的评论。

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.

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

New Query:

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

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

浮萍、无处依2024-11-20 03:46:49

从编辑和评论反馈中,这是我认为您正在寻找的查询...最内部的预查询将获取帖子以及谁发起了帖子、评论以及谁发布了评论。此内部查询还预先排序,将最新评论排列在每个 postID 的顶部。使用该结果,我加入 sql 变量 (@variables),以使 @varRow 在每次新评论时增加,并在每次帖子 ID 更改时重置回 1(因此内部 PreQuery 按帖子 ID FIRST 排序) )。最后,使用 HAVING 子句使注释的 @varRow 计数 < 6 人将获得每个帖子的最多 5 分。

如果您想限制您尝试检索的帖子,我会在生成“PreQuery”的最内部应用 WHERE 子句(例如日期/时间,如果可用)。

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。由于最里面的查询对所有元素/表进行了全面的联接,因此所有元素/表都会随之而来...

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 个的注释。

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.

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

--- 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)

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.

揽月2024-11-20 03:46:49

您需要为派生表指定一个别名:

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 derived_table_alias
ORDER BY posts.createdAt

You need to give your derived table an alias:

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 derived_table_alias
ORDER BY posts.createdAt
多情出卖2024-11-20 03:46:49

由于您使用的是子查询(这就是“派生表”的含义),因此它确实必须有一个别名。因此,您需要做的就是:

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 some_alias --This is what's triggering the error
ORDER BY posts.createdAt

即使您没有从子查询中进行选择,而只是将其用作过滤器,您也必须为其添加别名。

Since you're using a subquery (which is what it means by "derived table"), it must indeed have an alias. Thus, all you need to do is:

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 some_alias --This is what's triggering the error
ORDER BY posts.createdAt

Even if you're not selecting from the subquery, and just using it as a filter, you have to alias it.

°如果伤别离去2024-11-20 03:46:49

错误


  1. 在子查询后添加别名。
    示例:SELECT * FROM foo JOIN (select * from bar) AS

  2. 确保 posts 表中有一个字段,并且该字段被称为创建于。我不确定 MySQL 是否区分大小写,但您发布的错误显示 createdat (“A”小写)

  3. 您有两个 LEFT JOIN,但只有一个 ON 语句。如果没有连接的钩子,连接就什么都不是。示例:

    <前><代码>选择*
    FROM foo JOIN bar ON (foo.id=bar.id)
    左连接(从 foobar 中选择 *)AS baz **ON foo.id=baz.id**

  4. 为了连接某个字段,该字段需要存在于连接涉及的表中。因此,在上面的示例中,如果将 foo.idbaz.id 匹配,则需要在子查询(baz)中返回 id

By Error


  1. Add an alias following your subquery.
    Example: SELECT * FROM foo JOIN (select * from bar) AS <alias_here>

  2. Make sure you have a field in the posts table and that it is called createdAt. I'm not sure MySQL is case-sensitive, but the error you posted says createdat (with the 'A' lowercased)

  3. You have two LEFT JOINs, but only one ON statement. A join isn't anything without the hook to join it on. Example:

    SELECT *   
    FROM foo JOIN bar ON (foo.id=bar.id) 
    LEFT JOIN (select * from foobar) AS baz **ON foo.id=baz.id**
    
  4. In order to join on a field, the field needs to be present in the table involved in the join. So in the above example, if you match foo.id with baz.id, id needs to be returned in the subquery (baz).

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文