你的位置:首页 > Java教程

[Java教程]mybatis Result Maps对结果分组3


转载请注明: 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