mysql分页并分组查询数据

1.查出前五本price最高的书

SELECT title,price 
FROM book  
WHERE
	( SELECT count( 1 ) FROM book AS b WHERE b.price > book.price )< 5;
	
-- 在不分组的情况下也可以用limit order实现
SELECT title, price  FROM book  ORDER BY price DESC  LIMIT 5

2.查询每个班（class_id）最大的两个id（分组）

SELECT * FROM edu AS b 
WHERE
	( SELECT count( 1 ) FROM edu AS t WHERE t.id > b.id  and t.class_id = b.class_id)<2
	and ( SELECT count( 1 ) FROM edu AS t WHERE t.id > b.id  and t.class_id = b.class_id)>=0;
	
-- 或者用between
SELECT * FROM edu AS b 
WHERE
	( SELECT count( 1 ) FROM edu AS t WHERE t.id > b.id  and t.class_id = b.class_id) BETWEEN 0 AND 2-1;
	
-- 可以分页： pageSize=3 pageNum=1(第二页)
SELECT * FROM edu AS b 
WHERE
	( SELECT count( 1 ) FROM edu AS t WHERE t.id > b.id  and t.class_id = b.class_id) BETWEEN pageNum*pageSize AND (pageNum+1)*pageSize-1;

3.查询每个班前两名

student 表和score表

SELECT
outt.id	,name,score ,class_id
FROM
	( SELECT c.id, name ,class_id,score from student t  JOIN score c on t.id = c.id) as outt
WHERE
	( SELECT count( 1 ) 
	FROM ( SELECT c.id, name ,class_id,score from student t  JOIN score c on t.id = c.id) as inn WHERE outt.score < inn.score
and inn.class_id =outt.class_id 	)<2

相关表sql如下

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `title` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `price` bigint(10) NULL DEFAULT NULL,
  PRIMARY KEY (`title`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('a', 4);
INSERT INTO `book` VALUES ('b', 15);
INSERT INTO `book` VALUES ('c', 7);
INSERT INTO `book` VALUES ('d', 2);
INSERT INTO `book` VALUES ('e', 66);
INSERT INTO `book` VALUES ('f', 1);
INSERT INTO `book` VALUES ('g', 33);

-- ----------------------------
-- Table structure for edu
-- ----------------------------
DROP TABLE IF EXISTS `edu`;
CREATE TABLE `edu`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `class_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_class_id`(`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of edu
-- ----------------------------
INSERT INTO `edu` VALUES (1, '1', 1);
INSERT INTO `edu` VALUES (2, '2', 1);
INSERT INTO `edu` VALUES (3, '3', 1);
INSERT INTO `edu` VALUES (4, '4', 1);
INSERT INTO `edu` VALUES (8, '8', 2);
INSERT INTO `edu` VALUES (9, '9', 2);
INSERT INTO `edu` VALUES (10, '10', 2);
INSERT INTO `edu` VALUES (11, '11', 2);
INSERT INTO `edu` VALUES (12, '12', 2);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `score` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 99);
INSERT INTO `score` VALUES (2, 45);
INSERT INTO `score` VALUES (3, 667);
INSERT INTO `score` VALUES (4, 675);
INSERT INTO `score` VALUES (5, 33);
INSERT INTO `score` VALUES (6, 55);
INSERT INTO `score` VALUES (7, 567);
INSERT INTO `score` VALUES (8, 56);
INSERT INTO `score` VALUES (9, 123);
INSERT INTO `score` VALUES (10, 23);
INSERT INTO `score` VALUES (11, 2345);
INSERT INTO `score` VALUES (12, 44);
INSERT INTO `score` VALUES (13, 555);
INSERT INTO `score` VALUES (14, 123);
INSERT INTO `score` VALUES (15, 98);
INSERT INTO `score` VALUES (16, 1);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `class_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '1', 1);
INSERT INTO `student` VALUES (2, '2', 1);
INSERT INTO `student` VALUES (3, '3', 1);
INSERT INTO `student` VALUES (4, '4', 1);
INSERT INTO `student` VALUES (5, '5', 2);
INSERT INTO `student` VALUES (6, '6', 2);
INSERT INTO `student` VALUES (7, '7', 2);
INSERT INTO `student` VALUES (8, '8', 3);
INSERT INTO `student` VALUES (9, '9', 3);
INSERT INTO `student` VALUES (10, '10', 3);
INSERT INTO `student` VALUES (11, '11', 3);
INSERT INTO `student` VALUES (12, '12', 3);
INSERT INTO `student` VALUES (13, '13', 4);
INSERT INTO `student` VALUES (14, '14', 4);
INSERT INTO `student` VALUES (15, '15', 4);
INSERT INTO `student` VALUES (16, '16', 4);

SET FOREIGN_KEY_CHECKS = 1;