这篇文章主要介绍了详解MySQL分组排序求Top
表结构:
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`grp` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
1.在where中可以通过子查询创造一个新的变量来过滤
SELECT * FROM score AS t3
WHERE (
SELECT COUNT(*) FROM score AS t1
LEFT JOIN score AS t2
ON t1.grp = t2.grp AND t1.num < t2.num
WHERE t1.id = t3.id
) < 3
ORDER BY t3.grp ASC, num DESC
2:如果希望将序号也带上
SELECT *, (SELECT count(1)+1 from score tb WHERE ta.grp=tb.grp and ta.num<tb.num) as row_num from score ta order by ta.grp,num desc
3.mysql8以后可以通过窗口函数实现
SELECT * ,row_number() over (partition by grp order by num desc ) as row_num from score
来源:
https://www.jb51.net/article/117859.htm
https://www.toutiao.com/i6613173233380229636/