在单个查询中为每个 DISTINCT 记录选择多条记录

我需要为每个不同的记录选择几行。几乎就像这里问的那样在 SQL Server 2008 中为每个不同 ID 选择前 n 条记录 https://stackoverflow.com/questions/3364224/select-first-n-records-for-each-distinct-id-in-sql-server-2008，虽然我使用MySQL。

在这种情况下，可以通过运行 21 个查询来实现目的：1 个常规查询和 20 个查询来获取子记录，即如下所示：

SELECT DISTINCT `user_id`
FROM `posts`
WHERE `deleted` = '0'
ORDER BY `user_id` ASC
LIMIT 20

...选择所需的所有行，然后

SELECT *
FROM `posts`
WHERE `deleted` = '0'
AND `user_id` = ?
ORDER BY `id` DESC
LIMIT 5

...在第一个查询选择的每一行的循环中。

基本上，我需要获取每个用户的 5 个帖子。我需要在单个查询中完成此操作。这posts设置只是一个例子，我做了这个，所以希望它更容易理解我的需要。

我从以下查询开始：

SELECT * 
FROM `posts` 
WHERE `user_id` 
IN (
    SELECT DISTINCT `user_id` 
    FROM `posts` 
    ORDER BY `user_id` DESC 
    LIMIT 4
) 
LIMIT 5

但我明白了#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' error.

所以我尝试过JOIN像建议的想法here https://stackoverflow.com/a/2856430/722036:

SELECT  posts.id,
        posts.user_id,
        NULL
FROM    (
        SELECT  posts.user_id
        FROM    posts
        WHERE   posts.deleted = 0
        LIMIT 20
        ) q
JOIN    posts
ON      posts.user_id = q.user_id

我还按照建议尝试了几个嵌套查询here https://stackoverflow.com/a/7124492/722036:

SELECT *
FROM posts 
WHERE user_id IN (
      SELECT * FROM (
            SELECT user_id 
            FROM posts 
            LIMIT 20
      ) 
      as t);

以及网上找到的其他解决方案。但它们要么不起作用，要么只是简单地从数据库中选择前 N 行（无论条件和出于某种原因的连接如何）。尝试过LEFT JOIN, RIGHT JOIN, even INNER JOIN，但仍然没有成功。

请帮忙。

UPDATE忘了说该表的大小约为 5GB。

UPDATE尝试了子子查询：

SELECT * 
FROM `posts` 
WHERE
  `user_id` IN ( SELECT `user_id` FROM (
     SELECT DISTINCT `user_id` 
     FROM `posts` 
    ORDER BY `user_id` DESC 
    LIMIT 4 ) limit_users
  ) 
LIMIT 5

与上面相同，它返回以下内容：

+----+---------+------+
| id | user_id | post |
+----+---------+------+
|  1 |       1 |    a |
+----+---------+------+
|  2 |       1 |    b |
+----+---------+------+
|  3 |       1 |    c |
+----+---------+------+
| .. |      .. |   .. |

IE。 5（这是外部的LIMIT设置为）同一用户的行。奇怪的是，如果我单独运行子查询和子子查询：

    SELECT `user_id` FROM (
     SELECT DISTINCT `user_id` 
     FROM `posts` 
    ORDER BY `user_id` DESC 
    LIMIT 4 ) limit_users

我得到 4 个不同的值：

+---------+
| user_id |
+---------+
|       1 |
+---------+
|       2 |
+---------+
|       3 |
+---------+
|       4 |
+---------+

您必须使用变量，对有序查询执行两种不同的计数：一种是针对每个用户的帖子数量，另一种是针对用户：

SELECT posts_counts.*
FROM (
  SELECT
    posts.*,
    @post_count:=case when @prec_user_id=user_id then @post_count+1 else 1 end as pc,
    case when @prec_user_id<>user_id then @user_count:=@user_count+1 else @user_count end as uc,
    @prec_user_id:=user_id
  FROM
    posts,
    (select @prec_user_id:=0, @user_count:=0, @post_count:=0) counts
  ORDER BY
    posts.user_id ) posts_counts
WHERE pc<5 and uc<4

EDIT:您也可以考虑尝试以下查询：

SELECT * 
FROM `posts` 
WHERE
  `user_id` IN ( SELECT user_id FROM (
     SELECT DISTINCT `user_id` 
     FROM `posts` 
    ORDER BY `user_id` DESC 
    LIMIT 4 ) limit_users
  ) 
LIMIT 5

（这只会从每个选定用户的所有帖子中选择 5 个帖子，所以它仍然不是您需要的，但它使用了一个技巧，在子子查询中使用 LIMIT）

EDIT2:下一个查询将限制 20 个用户中每人 5 个帖子：

select posts_limited.*
from (
  select
    posts.*,
    @row:=if(@last_user=posts.user_id, @row+1, 1) as row,
   @last_user:=posts.user_id
  from
    posts inner join
    (select user_id from
      (select distinct user_id
       from posts
       order by user_id desc
       LIMIT 20) limit_users
    ) limit_users
    on posts.user_id = limit_users.user_id,
    (select @last_user:=0, @row:=0) r
  ) posts_limited
  where row<=5