在 mysql 中使用左连接限制
以下查询选择所有帖子和每个帖子的所有者、属于每个帖子的所有评论以及每个评论的所有者。
我只需要检索每个帖子 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 技术交流群。
发布评论
评论(4)
您需要为派生表指定一个别名:
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
由于您使用的是子查询(这就是“派生表”的含义),因此它确实必须有一个别名。因此,您需要做的就是:
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
即使您没有从子查询中进行选择,而只是将其用作过滤器,您也必须为其添加别名。
错误
在子查询后添加别名。
示例:SELECT * FROM foo JOIN (select * from bar) AS
确保
posts
表中有一个字段,并且该字段被称为创建于
。我不确定 MySQL 是否区分大小写,但您发布的错误显示createdat
(“A”小写)您有两个
LEFT JOIN
,但只有一个ON
语句。如果没有连接的钩子,连接就什么都不是。示例:<前><代码>选择*
FROM foo JOIN bar ON (foo.id=bar.id)
左连接(从 foobar 中选择 *)AS baz **ON foo.id=baz.id**为了连接某个字段,该字段需要存在于连接涉及的表中。因此,在上面的示例中,如果将
foo.id
与baz.id
匹配,则需要在子查询(baz)中返回id
。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
从编辑和评论反馈中,这是我认为您正在寻找的查询...最内部的预查询将获取帖子以及谁发起了帖子、评论以及谁发布了评论。此内部查询还预先排序,将最新评论排列在每个 postID 的顶部。使用该结果,我加入 sql 变量 (@variables),以使 @varRow 在每次新评论时增加,并在每次帖子 ID 更改时重置回 1(因此内部 PreQuery 按帖子 ID FIRST 排序) )。最后,使用 HAVING 子句使注释的 @varRow 计数 < 6 人将获得每个帖子的最多 5 分。
如果您想限制您尝试检索的帖子,我会在生成“PreQuery”的最内部应用 WHERE 子句(例如日期/时间,如果可用)。
--- 编辑 --- 每条评论
我认为您所说的“家长帖子”与评论相关联是因为它们直接具有帖子 ID。由于最里面的查询对所有元素/表进行了全面的联接,因此所有元素/表都会随之而来...
一旦全部完成并按常见帖子 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".
--- 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...
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.