mysql - 选择每组的最大行数

Question

我有一个简单的用户记录表。允许一个电子邮件地址有多个条目。我现在想创建一个将返回所有记录的查询，但将重复条目的数量限制为设定的数量，例如每个电子邮件地址 5 个。

我将使用此查询的最终结果来创建一个新表。

我唯一的想法是使用 PHP 并选择一个唯一电子邮件地址列表，然后我将循环使用此列表来查询表并将其限制为 5，但我的表中有大约 65k 个唯一电子邮件，并且总记录约 284k 行。

有什么方法可以在 MySQL 中完成吗？

这是表结构。

first_name, last_name, email
John, smith, js@email.com
john, smith, js@email.com
John, smith, js@email.com
john, smith, js@email.com
Jane, smith, jbs@email.com
jane, smith, jbs@email.com
Jane, smith, jbs@email.com
john, smith, js@email.com

我想要一个返回相同记录但将重复项限制为我可以更改的参数的查询。在上面的示例中，假设最多允许 2 个重复项。我想得到

first_name, last_name, email
John, smith, js@email.com
john, smith, js@email.com
Jane, smith, jbs@email.com
jane, smith, jbs@email.com

希望这能更清楚地说明这一点。

谢谢。

Answer 1

这样试试

SELECT user_id, first_name, last_name, email
  FROM
(
  SELECT user_id, first_name, last_name, email,
  (
    SELECT 1 + COUNT(*)
      FROM users
     WHERE email = u.email
       AND user_id < u.user_id
  ) rnum     
    FROM users u
) q
 WHERE rnum <= 2 -- change 2 to a number of duplicates you need
 ORDER BY email, user_id

这是 SQLFiddle 演示