分组查询 每组查询前5条

按班级分组查询，每组查询出5条数据。

数据表结构如下：

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class` int(11) DEFAULT NULL COMMENT '班级',
  `name` text COMMENT '名姓',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test1
-- ----------------------------
INSERT INTO `test1` VALUES ('1', '2', 'a');
INSERT INTO `test1` VALUES ('2', '2', 'b');
INSERT INTO `test1` VALUES ('3', '2', 'c');
INSERT INTO `test1` VALUES ('4', '2', 'd');
INSERT INTO `test1` VALUES ('5', '2', 'e');
INSERT INTO `test1` VALUES ('6', '2', 'f');
INSERT INTO `test1` VALUES ('7', '2', 'g');
INSERT INTO `test1` VALUES ('8', '2', 'h');
INSERT INTO `test1` VALUES ('9', '3', 'i');
INSERT INTO `test1` VALUES ('10', '3', 'j');
INSERT INTO `test1` VALUES ('11', '3', 'k');
INSERT INTO `test1` VALUES ('12', '3', 'l');
INSERT INTO `test1` VALUES ('13', '3', 'm');
INSERT INTO `test1` VALUES ('14', '3', 'n');
INSERT INTO `test1` VALUES ('15', '3', 'o');
INSERT INTO `test1` VALUES ('16', '3', 'p');
INSERT INTO `test1` VALUES ('17', '4', 'q');
INSERT INTO `test1` VALUES ('18', '4', 'r');
INSERT INTO `test1` VALUES ('19', '4', 's');

 

 实现sql:

select t.* from test1 t  where 5>(select count(*) from test1 where class=t.class and id<t.id) order by id desc

因为2次select 的都是同一张表，所以需要用 "from test1 t"  区分下。 

查询结果如下图：