可以限制来自JOIN查询的结果吗? [英] Is it possible to LIMIT results from a JOIN query?

问题描述

我有一个查询，该查询当前在查询Post表的同时向左联接Comment表.它获取所有帖子及其各自的评论.但是，我想限制返回的评论数.我尝试添加一个子选择，但是如果我不将结果限制为1，就会遇到错误.我真的不确定如何在仅使用一个查询的情况下进行此操作.这可能吗?

I've got a query that currently queries a Post table while LEFT JOINing a Comment table. It fetches all Posts and their respective Comments. However, I want to limit the number of Comments returned. I tried adding a sub-select, but ran into errors if I didn't LIMIT the results to 1. I'm really not sure how to go about this while still using only one query. Is this possible?

推荐答案

假设您的表格如下所示，这应该使您的帖子包含每个帖子的三个最新注释:

This one should get your posts with the three most recent comments per post, assuming that your tables look like that:

发布:
id，post_text

评论:
id，post_id，comment_text

SELECT id, post_text, comment_text
FROM
(
    SELECT p.id, p.post_text, c.comment_text
           CASE
             WHEN @id != p.id THEN @row_num := 1
             ELSE @row_num := @row_num + 1
           END AS rank,
           @id := p.id
    FROM post p
    LEFT JOIN comment c ON ( c.post_id = p.id )
    JOIN ( SELECT @id:=NULL, @row_num:=0 ) x
    ORDER BY p.id,
             c.id DESC -- newest comments first
) y
WHERE rank <= 3;

子查询用于首先获取最近的评论并按帖子编号，而外部选择则删除较旧的评论.

The sub-query is used to get recent comments first and to number them per post, while the outer select removes older comments.

这篇关于可以限制来自JOIN查询的结果吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！