MySQL – 查询每个作者点赞数最多的三篇文章并返回所有结果

表结构

CREATE TABLE `videos` (
  `id` int(11) NOT NULL,
  `video_id` varchar(30) DEFAULT NULL,
  `like_count` int(11) DEFAULT NULL,
  `comment_count` int(11) DEFAULT NULL,
  `share_count` int(11) DEFAULT NULL,
  `intro` text,
  `play_url` varchar(300) DEFAULT NULL,
  `cover_url` varchar(300) DEFAULT NULL,
  `author_id` varchar(300) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询语句

SELECT 
    a.*
FROM
    videos AS a,
    (SELECT 
        GROUP_CONCAT(id order by like_count desc) AS ids
    FROM
        videos
    GROUP BY author_id) AS b
WHERE
    FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND 3
ORDER BY a.author_id ASC, a.like_count DESC;

参考文章：https://blog.csdn.net/Jacksun_huang/article/details/88869025