项目场景:
公司需要根据角色查询用户,用户与角色是一对多的关系
问题描述:
分页查询时,一页10条,第一页只有9条,第二页却有10条
原因分析:
原因是,采用left join 查询一对多关系,分页导致的,第一页有一个用户有两个角色
<select id="selectUserByCond" resultMap="adminUserDto">
SELECT
id, login, first_name, last_name, email, image_url, activated, lang_key,
created_by, created_date, last_modified_by, last_modified_date, ua.authority_name roles
FROM jhi_user u left join jhi_user_authority ua on ua.user_id = u.id
WHERE 1 = 1
<if test="cond.search != null and cond.search != ''">
and ( login = #{cond.search, jdbcType=VARCHAR}
or first_name like concat('%', #{cond.search, jdbcType=VARCHAR}, '%')
)
</if>
<if test="cond.roles != null and cond.roles.size() > 0">
and ua.authority_name in
<foreach collection="cond.roles" item="it" open="(" close=")" separator=",">
#{it,jdbcType=VARCHAR}
</foreach>
</if>
order by #{page.order}
limit #{page.offset}, #{page.size }
</select>
将第一页的查询sql打印出来,并执行
SELECT
id, login, first_name, last_name, email, image_url, activated, lang_key,
created_by, created_date, last_modified_by, last_modified_date, ua.authority_name roles
FROM jhi_user u left join jhi_user_authority ua on ua.user_id = u.id
WHERE 1 = 1
order by login
limit 0, 2
下图是执行结果,可以看到两条记录的id是一样的
解决方案:
先分页查询user id,再根据userid 查询用户,虽然效率上会慢很多
<select id="selectUserByCond" resultMap="adminUserDto">
select id, login, first_name, last_name, email, image_url, activated, lang_key,
created_by, created_date, last_modified_by, last_modified_date, ua.authority_name roles
FROM jhi_user u left join jhi_user_authority ua on ua.user_id = u.id
where u.id in
(
SELECT
distinct a. id
from (
select distinct id
<if test="page.orderColumn != null and page.orderColumn.size() > 0">
<foreach collection="page.orderColumn" item="it" open="," close="" separator=",">
#{it,jdbcType=VARCHAR}
</foreach>
</if>
FROM jhi_user u left join jhi_user_authority ua on ua.user_id = u.id
WHERE 1 = 1
<if test="cond.search != null and cond.search != ''">
and ( login = #{cond.search, jdbcType=VARCHAR}
or first_name like concat('%', #{cond.search, jdbcType=VARCHAR}, '%')
)
</if>
<if test="cond.roles != null and cond.roles.size() > 0">
and ua.authority_name in
<foreach collection="cond.roles" item="it" open="(" close=")" separator=",">
#{it,jdbcType=VARCHAR}
</foreach>
</if>
order by #{page.order}
limit #{page.offset}, #{page.size }
) a
)
</select>
SELECT id,
login,
first_name,
last_name,
email,
image_url,
activated,
lang_key,
created_by,
created_date,
last_modified_by,
last_modified_date,
ua.authority_name roles
FROM jhi_user u
left join jhi_user_authority ua on ua.user_id = u.id
where u.id in (
select a.id
from (
select distinct id, login
from jhi_user u
left join jhi_user_authority ua on ua.user_id = u.id
order by login
limit 0, 2
) a
)
查询结果如下
结果正确,问题解决