mysql-group by 分页 mysql数据库

测试数据

CREATE TABLE `book_comment` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `like_num` int(10) NOT NULL DEFAULT '0' COMMENT '点赞数', `content` varchar(200) NOT NULL, `addtime` datetime NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `addtime` (`addtime`), KEY `uid_addtime` (`user_id`,`addtime`) ) ENGINE=InnoDB COMMENT='书本评论表'; INSERT INTO `book_comment` VALUES ('1', '1', '0', '评论1', '2017-05-17 00:00:00'); INSERT INTO `book_comment` VALUES ('2', '1', '2', '评论2', '2017-05-17 00:03:01'); INSERT INTO `book_comment` VALUES ('3', '2', '3', '评论3', '2017-05-17 00:03:02'); INSERT INTO `book_comment` VALUES ('4', '2', '4', '评论4', '2017-05-17 00:00:03'); INSERT INTO `book_comment` VALUES ('5', '3', '0', '评论5', '2017-05-17 00:00:04'); INSERT INTO `book_comment` VALUES ('6', '1', '1', '评论6', '2017-05-17 00:00:05'); INSERT INTO `book_comment` VALUES ('7', '4', '0', '评论7', '2017-05-17 00:00:06'); INSERT INTO `book_comment` VALUES ('8', '4', '2', '评论8', '2017-05-17 00:00:07'); INSERT INTO `book_comment` VALUES ('9', '4', '0', '评论9', '2017-05-17 00:00:08'); INSERT INTO `book_comment` VALUES ('10', '4', '3', '评论10', '2017-05-17 00:00:09'); INSERT INTO `book_comment` VALUES ('11', '3', '2', '评论11', '2017-05-17 00:00:10'); CREATE TABLE `book_comment_like` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `comment_id` int(10) unsigned NOT NULL COMMENT '评论表id', `like_name` varchar(200) NOT NULL COMMENT '点赞用户', `addtime` datetime NOT NULL, PRIMARY KEY (`id`), KEY `comment_id` (`comment_id`), KEY `addtime` (`addtime`) ) ENGINE=InnoDB COMMENT='书本评论点赞表'; INSERT INTO `book_comment_like` VALUES ('1', '1', '点赞用户1', '2017-05-17 00:03:01'); INSERT INTO `book_comment_like` VALUES ('2', '1', '点赞用户2', '2017-05-17 00:03:01'); INSERT INTO `book_comment_like` VALUES ('3', '2', '点赞用户3', '2017-05-17 00:03:02'); INSERT INTO `book_comment_like` VALUES ('4', '2', '点赞用户4', '2017-05-17 00:03:02'); INSERT INTO `book_comment_like` VALUES ('5', '2', '点赞用户5', '2017-05-17 00:03:02'); INSERT INTO `book_comment_like` VALUES ('6', '2', '点赞用户6', '2017-05-17 00:00:03'); INSERT INTO `book_comment_like` VALUES ('7', '3', '点赞用户7', '2017-05-17 00:00:04'); INSERT INTO `book_comment_like` VALUES ('8', '5', '点赞用户8', '2017-05-17 00:00:06'); INSERT INTO `book_comment_like` VALUES ('9', '5', '点赞用户9', '2017-05-17 00:00:07'); INSERT INTO `book_comment_like` VALUES ('10', '5', '点赞用户10', '2017-05-17 00:00:08');

【mysql-group by 分页】关联表一对多。一条评论会有多个用户点赞。
需要分页显示，根据评论表(主表)分页，并且显示点赞用户详情
1：方案一先查评论表(根据分页要求)，然后依次遍历每条评论信息，获取对应的点赞用户
2：方案二

mysql> SELECT a.*, b.* FROM ( SELECT * FROM book_comment GROUP BY id LIMIT 0, 5 ) a LEFT JOIN book_comment_like b ON a.id = b.comment_id; +----+---------+----------+---------+---------------------+------+------------+------------+---------------------+ | id | user_id | like_num | content | addtime| id| comment_id | like_name| addtime| +----+---------+----------+---------+---------------------+------+------------+------------+---------------------+ |1 |1 |0 | 评论1| 2017-05-17 00:00:00 |1 |1 | 点赞用户1| 2017-05-17 00:03:01 | |1 |1 |0 | 评论1| 2017-05-17 00:00:00 |2 |1 | 点赞用户2| 2017-05-17 00:03:01 | |2 |1 |2 | 评论2| 2017-05-17 00:03:01 |3 |2 | 点赞用户3| 2017-05-17 00:03:02 | |2 |1 |2 | 评论2| 2017-05-17 00:03:01 |4 |2 | 点赞用户4| 2017-05-17 00:03:02 | |2 |1 |2 | 评论2| 2017-05-17 00:03:01 |5 |2 | 点赞用户5| 2017-05-17 00:03:02 | |2 |1 |2 | 评论2| 2017-05-17 00:03:01 |6 |2 | 点赞用户6| 2017-05-17 00:00:03 | |3 |2 |3 | 评论3| 2017-05-17 00:03:02 |7 |3 | 点赞用户7| 2017-05-17 00:00:04 | |5 |3 |0 | 评论5| 2017-05-17 00:00:04 |8 |5 | 点赞用户8| 2017-05-17 00:00:06 | |5 |3 |0 | 评论5| 2017-05-17 00:00:04 |9 |5 | 点赞用户9| 2017-05-17 00:00:07 | |5 |3 |0 | 评论5| 2017-05-17 00:00:04 |10 |5 | 点赞用户10 | 2017-05-17 00:00:08 | |4 |2 |4 | 评论4| 2017-05-17 00:00:03 | NULL | NULL| NULL| NULL| +----+---------+----------+---------+---------------------+------+------------+------------+---------------------+ 11 rows in set