mysql 点赞计数,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.youkuaiyun.com/Jacksun_huang/article/details/88869025