是否可以限制联接查询的结果?

假设您的表看起来是这样的,那么这篇文章应该为您的文章提供每篇文章的三条最新评论:

邮递 :
id , post_text

评论 :
身份证件 , 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;

子查询用于首先获取最近的评论并为每个帖子编号,而外部选择则删除较旧的评论。

除非您有一些方便的值可以筛选,否则不能限制联接(例如。 where pos between 1 and 5 )您可以分别选择第一条注释、第二条注释、第三条注释等,并合并结果。丑陋的东西,比如:

select This, That
from Post
left join (
  select Some
  from Comment
  where PostId = Post.Id
  order by CreatedDate
  limit 1,1
) x on 1=1

union all

select This, That
from Post
inner join (
  select Some
  from Comment
  where PostId = Post.Id
  order by CreatedDate
  limit 2,1
) x on 1=1

union all

select This, That
from Post
inner join (
  select Some
  from Comment
  where PostId = Post.Id
  order by CreatedDate
  limit 3,1
) x on 1=1