SQL查询:如何获取用户和他们的三个最新帖子

Question

假设我有两个表的简单模式，users和posts。posts表有一个指向users的外键，用来指示文章的作者。

同样，假设我想列出用户和他们最近的3篇文章。我可以在O(n)个查询中做到这一点(1用于列出用户，1用于每个用户获取他们的帖子)，但是我如何在O(1)个查询中做到这一点呢?

一个查询一次获取用户和帖子，或者两个查询，一个获取用户，一个获取帖子。假设我将删除所有重复的用户数据。

Answer 1

您没有说明您的DBMS，所以这是ANSI SQL(被广泛的DBMS支持):

select *
from (
   select u.username,
          p.title, 
          row_number() over (partition by u.id order by p.post_time desc) as rn
   from users u
     join posts p on u.id = p.user_id
) t
where rn <= 3
order by u.username;

Answer 2

一个应该在大多数数据库上工作的自连接。这里假设post_time对每个用户都是唯一的。如果不是这样，那么您可以用on p2.id >= p.id替换on p2.post_time >= p.post_time。

select u.username, p.id, p.title
from user u join
(
    select p.id, p.title, p.user_id 
    from posts p join posts p2
    on p2.user_id = p.user_id
    and p2.post_time >= p.post_time
    group by p.id, p.title, p.user_id
    having count(*) <= 3
) p on u.id = p.user_id

Answer 3

在任何数据库中都可以工作:

select u.username, p.title, p.post_time
  from users u
  join posts p
    on u.id = p.user_id
 where p.post_time >=
       (select max(z.post_time)
          from posts z
         where z.user_id = p.user_id
           and z.post_time <
               (select max(y.post_time)
                  from posts y
                 where y.user_id = p.user_id
                   and y.post_time <
                       (select max(x.post_time)
                          from posts x
                         where x.user_id = p.user_id)))

Answer 4

SELECT *
FROM Users a
CROSS APPLY (
SELECT TOP 3 *
FROM posts z
WHERE a.User_ID = z.User_ID
ORDER BY z.post_time DESC
) b