测试数据
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
) 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