mybatis Result Maps对结果分组3--一对多使用limit
转载请注明: TheViper http://www.cnblogs.com/TheViper
前面两篇文章都是先把多张表的结果一并取出,然后用mybatis自动分组,实现一对多的结果集。但是,实际开发过程中,是需要对评论分页的,比如在mysql中使用limit.
SELECT articlecomment.articlecomment_id,articlecomment.commenttime,articlecomment.commentcontent,articlecomment.id as commentuser_id ,articlereply.articlereply_id,articlereply.replytime,articlereply.replycontent,articlereply.id as replyuser_id ,u1.name AS comment_user,u2.name AS reply_user FROM articlecomment LEFT JOIN articlereply ON articlereply.comment_id=articlecomment.articlecomment_id INNER JOIN USER AS u1 ON u1.id=articlecomment.id INNER JOIN USER AS u2 ON u2.id=articlereply.id ORDER BY commenttime DESC,replytime DESC
可以看到评论id为1和2的评论都有3条回复,这时对评论分页直接用limit,比如limit 2,就取不到评论id为1的评论,然后mybatis自动分组后就只有一条评论。
解决方法是外面不用limit,在where条件中,对articlecomment_id使用in子查询,在in子查询里面用limit,取出指定limit的满足条件还没有外连接的评论的articlecomment_id。
SELECT articlecomment.articlecomment_id,articlecomment.commenttime,articlecomment.commentcontent,articlecomment.id AS commentuser_id ,articlereply.articlereply_id,articlereply.replytime,articlereply.replycontent,articlereply.id AS replyuser_id ,u1.name AS comment_user,u2.name AS reply_user FROM articlecomment LEFT JOIN articlereply ON articlereply.comment_id=articlecomment.articlecomment_id INNER JOIN USER AS u1 ON u1.id=articlecomment.id LEFT JOIN USER AS u2 ON u2.id=articlereply.id WHERE articlecomment.articlecomment_id IN(SELECT articlecomment1.articlecomment_id FROM articlecomment AS articlecomment1 WHERE articlecomment1.article_id=1 ORDER BY articlecomment1.articlecomment_id DESC LIMIT 2 ) ORDER BY articlecomment.articlecomment_id DESC,replytime DESC
这时会显示错误,This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'。
很容易搜到鸟哥的这篇MySQL也真是让人郁闷(关于子查询中使用limit)。在外面加一层就可以了。
SELECT articlecomment.articlecomment_id,articlecomment.commenttime,articlecomment.commentcontent,articlecomment.id AS commentuser_id ,articlereply.articlereply_id,articlereply.replytime,articlereply.replycontent,articlereply.id AS replyuser_id ,u1.name AS comment_user,u2.name AS reply_user FROM articlecomment LEFT JOIN articlereply ON articlereply.comment_id=articlecomment.articlecomment_id INNER JOIN USER AS u1 ON u1.id=articlecomment.id LEFT JOIN USER AS u2 ON u2.id=articlereply.id WHERE articlecomment.articlecomment_id IN(SELECT articlecomment1.articlecomment_id FROM (SELECT * FROM articlecomment AS articlecomment2 WHERE articlecomment2.article_id=1 ORDER BY articlecomment2.articlecomment_id DESC LIMIT 2) AS articlecomment1 ) ORDER BY articlecomment.articlecomment_id DESC,replytime DESC
posted on 2015-07-27 20:19 TheViper_ 阅读(5778) 评论(0) 编辑 收藏 举报