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_  阅读(5756)  评论(0编辑  收藏  举报