测试数据
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'); 关联表一对多。一条评论会有多个用户点赞。
须要分页显示，依据评论表(主表)分页，并且显示点赞用户详情
1：计划一
先查评论表(依据分页要求)，而后顺次遍历每条评论信息，获取对应的点赞用户
2：计划二
mysql> SELECT a.*, b.*FROM ( SELECT * FROM book_comment GROUP BY id LIMIT 0, 5 ) aLEFT 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