mysql中实现over partiton by 进行分组排序取topN

创表

CREATE TABLE `t1` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`class` varchar(255) DEFAULT NULL,`score` int(255) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;

插入数据

INSERT INTO `t1` VALUES ('1', 'george', '一班', '100');INSERT INTO `t1` VALUES ('2', 'georgedage', '一班', '98');INSERT INTO `t1` VALUES ('3', 'lucy', '一班', '89');INSERT INTO `t1` VALUES ('4', 'lili', '一班', '58');INSERT INTO `t1` VALUES ('5', '鲁智深', '二班', '97');INSERT INTO `t1` VALUES ('6', '武松', '二班', '99');INSERT INTO `t1` VALUES ('7', '阮小二', '二班', '85');INSERT INTO `t1` VALUES ('8', '宋江', '二班', '46');INSERT INTO `t1` VALUES ('9', '关羽', '三班', '99');INSERT INTO `t1` VALUES ('10', '刘备', '三班', '78');INSERT INTO `t1` VALUES ('11', '张飞', '三班', '92');INSERT INTO `t1` VALUES ('12', '赵云', '三班', '100');

表数据展示

简单的分组内进行排序

select * from t1 group by class,score order by class,score desc

结果展示

那么分组排序后进行取top怎么做呢？

两种写法，欢迎交流~

select t.id,t.name,t.class,t.score from t1 t left join t1 t_left on t.class = t_left.class and t.score < t_left.score group by t.id,t.name,t.class,t.scorehaving count(t.id)<3order by t.class,t.score desc

select t.id,t.name,t.class,t.score from t1 t where (select count(*) from t1 where class = t.class and score > t.score) < 3order by t.class,t.score desc

结果展示

如果还有什么好用的方法，欢迎留言！