MyBatis关联查询分页
背景:单表好说,假如是MySQL的话,直接limit就行了。
对于多对多或者一对多的情况,假如分页的对象不是所有结果集,而是对一边分页,那么可以采用子查询分页,再与另外一张表关联查询,比如:
select * from (select * from teacher t limit 0, 2) tt left join clazz ttt on tt.id = ttt.teacher_id;
先对teacher分页,再关联查询。但是这样一来就不太好用mybatis的分页插件统一分页,并且需要自己去写一条count语句(插件虽然也执行了count语句,但是不需要我们去手动写一条),不太方便。不知道有没有什么好的方式解决。PageHelper对单表分页倒是比较犀利,像这种情形就不太好处理。
还有一种性能比较低,但是比较简单的方式就是利用select标签,进行n+1查询。
另外,主表和子表都带有查询条件的场景:
分页:
SELECT u.*, a.id aid, a.NAME aname, a.user_id, c.id cid, c.NAME cname, c.addr_id FROM smart_user u LEFT JOIN smart_addr a ON u.id = a.user_id LEFT JOIN smart_city c ON a.id = c.addr_id WHERE u.id IN ( SELECT u.id FROM ( SELECT DISTINCT u.id, u.NAME FROM smart_user u LEFT JOIN smart_addr a ON u.id = a.user_id LEFT JOIN smart_city c ON a.id = c.addr_id WHERE u.NAME = 'w.dehai' AND a.user_id = 1 AND c.NAME = '成都' ORDER BY u.id DESC, u.NAME ASC LIMIT 0, 2 ) u ) AND u.NAME = 'w.dehai' AND a.user_id = 1 AND c.NAME = '成都' ORDER BY u.id DESC, u.NAME ASC
统计:
SELECT count( DISTINCT u.id ) __count__ FROM smart_user u LEFT JOIN smart_addr a ON u.id = a.user_id WHERE u.NAME = 'w.dehai' AND a.user_id = 1