mysql 分组 查每组前_分组查询 每组查询前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

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

查询结果如下图：