MySQL - 将字段最多出现 5 次

Question

背景:

我运营一个平台，允许用户关注创作者并查看他们的内容。

以下查询成功显示按受欢迎程度排序的 50 个帖子。还有一些其他逻辑不显示用户已保存/删除的帖子，但这与此问题无关。

问题:

如果某个创作者特别受欢迎(受欢迎度)，返回的前 50 条帖子将几乎全部由该创作者创作。

这会扭曲结果，因为理想情况下返回的 50 篇帖子不会有利于某一特定作者。

问题:

如何限制它，以便作者(使用字段 posted_by)返回的次数不超过 5 次。如果某个特定作者被退回，可能会更少，但绝对不能超过 5 次。

最终仍应按流行度 DESC排序

SELECT * 
FROM   `source_posts` 
WHERE  `posted_by` IN (SELECT `username` 
                       FROM   `source_accounts` 
                       WHERE  `id` IN (SELECT `sourceid` 
                                       FROM   `user_source_accounts` 
                                       WHERE  `profileid` = '100')) 
       AND `id` NOT IN (SELECT `postid` 
                        FROM   `user_posts_removed` 
                        WHERE  `profileid` = '100') 
       AND `live` = '1' 
       AND `added` >= Date_sub(Now(), INTERVAL 1 month) 
       AND `popularity` > 1 
ORDER  BY `popularity` DESC 
LIMIT  50 

谢谢。

编辑:

我使用的是 MySQL 版本 5.7.24，因此不幸的是 row_number() 函数在此实例中不起作用。

Answer 1

在 MySQL 8+ 中，您只需使用 row_number():

select sp.*
from (select sp.*,
             row_number() over (partition by posted_by order by popularity desc) as seqnum
      from source_posts sp
     ) sp
where seqnum <= 5
order by popularity desc
limit 50;

我不确定您的查询的其余部分在做什么，因为您的问题中没有描述它。当然，您可以添加其他过滤条件或加入。

编辑:

在早期版本中，您可以使用变量:

select sp.*
from (select sp.*,
             (@rn := if(@p = posted_by, @rn + 1,
                        if(@p := posted_by, 1, 1)
                       )
             ) as rn
      from (select sp.*
            from source_posts sp
            order by posted_by, popularity desc
           ) sp cross join
           (select @p := '', @rn := 0) params
     ) sp
where rn <= 5
order by popularity desc
limit 50;