mysql 分组同时分页

创建表

DROP TABLE IF EXISTS `video`;
CREATE TABLE `video` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '视频id',
  `title` varchar(155) NOT NULL COMMENT '视频标题',
  `description` varchar(255) DEFAULT '' COMMENT '视频描述',
  `pid` int(10) NOT NULL DEFAULT '0' COMMENT '课程ID',
  `url` varchar(255) NOT NULL COMMENT '视频路径',
  `sort` int(10) DEFAULT '1' COMMENT '排序值',
  `status` tinyint(2) NOT NULL DEFAULT '1' COMMENT '状态：1启用2禁用',
  `create_time` int(10) NOT NULL DEFAULT '0' COMMENT '录入时间',
  `update_time` int(10) DEFAULT '0' COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

填充数据

INSERT INTO `video` VALUES ('4', '故宫的历史', '', '4', 'https://pingtuan.nos-eastchina1.126.net/video/10560c41a82354956d79635ea11e8c3d.mp4', '3', '1', '1557715954', '1557718230');
INSERT INTO `video` VALUES ('3', '恐龙之谜', '', '3', 'https://pingtuan.nos-eastchina1.126.net/video/10560c41a82354956d79635ea11e8c3d.mp4', '4', '1', '1557715954', '1557718230');
INSERT INTO `video` VALUES ('2', '大禹治水', '', '5', 'http://vjs.zencdn.net/v/oceans.mp4', '5', '1', '1557715954', '1557718230');
INSERT INTO `video` VALUES ('1', '皇帝和炎帝', '', '5', 'https://pingtuan.nos-eastchina1.126.net/video/10560c41a82354956d79635ea11e8c3d.mp4', '2', '1', '1557715954', '1557718230');
INSERT INTO `video` VALUES ('5', '火星', '', '7', 'https://pingtuan.nos-eastchina1.126.net/video/10560c41a82354956d79635ea11e8c3d.mp4', '1', '1', '1557715954', '1559287924');
INSERT INTO `video` VALUES ('6', '海洋有多大', '', '5', 'https://pingtuan.nos-eastchina1.126.net/video/10560c41a82354956d79635ea11e8c3d.mp4', '2', '1', '1557715954', '1557718230');
INSERT INTO `video` VALUES ('7', '海洋生物', '', '5', 'https://pingtuan.nos-eastchina1.126.net/video/10560c41a82354956d79635ea11e8c3d.mp4', '2', '1', '1557715954', '1559206671');
INSERT INTO `video` VALUES ('8', '走进物理', '', '6', 'https://pingtuan.nos-eastchina1.126.net/video/10560c41a82354956d79635ea11e8c3d.mp4', '2', '1', '1557715954', '1559206655');
INSERT INTO `video` VALUES ('9', '宇宙介绍', '', '7', 'https://pingtuan.nos-eastchina1.126.net/video/10560c41a82354956d79635ea11e8c3d.mp4', '2', '1', '1557715954', '1559206643');
INSERT INTO `video` VALUES ('10', '外星生物', '', '3', 'https://pingtuan.nos-eastchina1.126.net/video/10560c41a82354956d79635ea11e8c3d.mp4', '2', '1', '1557715954', '1559206632');
INSERT INTO `video` VALUES ('11', '牛顿', '关于物理大家牛顿的故事', '6', 'https://pingtuan.nos-eastchina1.126.net/video/10560c41a82354956d79635ea11e8c3d.mp4', '1', '1', '1558754832', '1559206625');
INSERT INTO `video` VALUES ('12', '真的有外星人吗', 'fs', '7', 'http://pt-static.yx090.com/video/dc3febdd3330cfa72bc0a333e48113e2.mp4', '1', '1', '1559202369', '1559287998');

SQL语句取出所有并分组

select id,title,pid,create_time,update_time,status,sort from snake_video v1 where(
	select count(1) from snake_video v2 where v1.pid =v2.pid
) order by v1.pid,v1.id;

点击查看语法解释
结果：
添加分页

select id,title,pid,create_time,update_time,status,sort from snake_video v1 where(
	select count(1) from snake_video v2 where v1.pid =v2.pid
) order by v1.pid,v1.id limit 0,5;

得到结果：