1 业务详情
rt,两张表user,score,一对多,以user为主表,需要根据user来进行分页查询.想要获取男性,以pageNum=3分页的学生信息和分数信息
2 表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`course` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`score` int(1) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
3 坑
需求: 查询男性,pageSize=3
在mybatis中,使用resultMap 进行映射,此处使用的是直接映射子表结果集,而不是使用select,防止出现n+1次查询问题.此查询问题,不懂的可以google
① 下面sql,为坑
select u.*,s.* from user u
left join score s on u.id=s.user_id
where u.gender='男'
limit 0,3;
很明显,对于小白来说,很大可能这么写,但是这样写,是获取不到想要结果的…
② why
其实很简单,最终获取到的是1对多的集,limit是对最终的临时表进行分页,即对最终一对多形成的结果进行分页,而不是根据主表数据分页.
4 解决方案
sql如下:
select u.*,s.* from
(select u1.* from user u1 where u1.gender='男' limit 0,3) u
left join score s on u.id=s.user_id
结果:
即,先对你想要分页的表进行分页和条件之后,再进行关联查询.这样的结果经过mybatis的resultMap映射后,即可获取到根据user分页的结果
5 注意
当你使用4的sql需要进行排序的时候,子查询需要排序,外面的查询也需要排序
错误sql:
select u.*,s.* from
(select u1.* from user u1 where u1.gender='男' order by u1.id desc limit 0,3) u
left join score s on u.id=s.user_id
错误sql结果:
正确sql:
select u.*,s.* from
(select u1.* from user u1 where u1.gender='男' order by u1.id desc limit 0,3) u
left join score s on u.id=s.user_id
order by u.id desc
正确sql结果: