按用户加载三个帖子 [英] Load three posts by user

问题描述

我希望加载所有帖子，首先按最新排序，但也将每个用户限制为三个.我不知道该怎么做！这是我当前用于创建表和选择帖子的 SQL.

I am looking to load all posts, sorted by newest first, but also limit them to three per user. I have no idea how to do that though! Here's the SQL I have currently to create the table and select the posts.

SELECT p.title, u.firstname, u.lastname
  FROM post p  
  JOIN user u
    ON p.user_id=u.id
 ORDER
    BY u.id, p.ctime DESC
#LIMIT TO 3 by user
;

推荐答案

这是你可以做的

select
u.*,
p.*
from
user u
left join
(
   select  
   p1.*
   FROM    
   post p1
   where    
    (
       select   
       count(*) 
       from
       post p2
       WHERE 
       p1.user_id = p2.user_id
       AND p1.id <= p2.id
    ) <= 3
   order by p1.id desc
) p ON  u.id = p.user_id
order by u.id 

从MySQL 加入后限制 LEFT JOIN 子查询

这篇关于按用户加载三个帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！