【mysql】【分组】后取每组的top2
1 DROP TABLE IF EXISTS `tb1`; 2 CREATE TABLE `tb1` ( 3 `id` int(11) NOT NULL AUTO_INCREMENT, 4 `a` varchar(255) DEFAULT NULL, 5 `b` varchar(255) DEFAULT NULL, 6 `content` varchar(255) DEFAULT NULL, 7 PRIMARY KEY (`id`) 8 ) 9 -- ---------------------------- 10 INSERT INTO `tb1` VALUES ('1', 'a', '1', 'aaa'); 11 INSERT INTO `tb1` VALUES ('2', 'a', '2', 'bbb'); 12 INSERT INTO `tb1` VALUES ('3', 'a', '3', 'ccc'); 13 INSERT INTO `tb1` VALUES ('4', 'b', '1', 'ddd'); 14 INSERT INTO `tb1` VALUES ('5', 'b', '2', 'eee');
代码:
1 select * 2 from tb1 k 3 where 2>(select count(*) from tb1 where k.a=a and id>k.id);
效果:
原理:
待续...
阿亮的笔记