MySQL分组排序求Top

这篇文章主要介绍了详解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/