【mysql】【分组】后取每组的top2

时间:2022-08-26 08:33:01
 DROP TABLE IF EXISTS `tb1`;
CREATE TABLE `tb1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` varchar(255) DEFAULT NULL,
`b` varchar(255) DEFAULT NULL,
`content` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
)
-- ----------------------------
INSERT INTO `tb1` VALUES ('', 'a', '', 'aaa');
INSERT INTO `tb1` VALUES ('', 'a', '', 'bbb');
INSERT INTO `tb1` VALUES ('', 'a', '', 'ccc');
INSERT INTO `tb1` VALUES ('', 'b', '', 'ddd');
INSERT INTO `tb1` VALUES ('', 'b', '', 'eee');

【mysql】【分组】后取每组的top2

代码:

 select *
from tb1 k
where 2>(select count(*) from tb1 where k.a=a and id>k.id);

效果:

【mysql】【分组】后取每组的top2

原理:

待续...