极端分页情况 对于主表的判断一定要在()里面做 ,这里主表是sx_user
select u.id,u.`name`,u.`password`,u.salt,u.area,u.department,u.real_name,
u.email,u.tel,u.remark,u.type_describe,u.user_status,u.valid_date,
u.create_by,u.update_by,u.gmt_create,u.gmt_update,mid.id as midId,mid.role_id as midRoleId,
r.id as roleId,r.role_name,r.`code`,r.description,r.gmt_create as role_gmt_create, r.gmt_modified as role_gmt_modified,r.is_deleted as role_is_deleted,
per.id as pid, per.permission_name , per.type as per_type from
( select * from sx_user
<where>
id != #{map.id} and user_status=0
<if test="map.userName!=null and map.userName!=''">
and name like concat('%',#{map.userName},'%')
</if>
<if test="map.realName!=null and map.realName!=''">
and real_name like concat('%',#{map.realName},'%')
</if>
<if test="map.department!=null and map.department!=''">
and department like concat('%',#{map.department})
</if>
<if test="map.area!='all'">
and area like concat('%',#{map.area},'%')
</if>
<if test="map.type!='all'">
and type_describe like concat('%',#{map.type},'%')
</if>
<if test="map.admin!='admin'">
and
name in
<foreach collection="map.set" open="(" close=")" separator="," index="index" item="item">
#{item}
</foreach>
</if>
</where>order by gmt_update desc limit #{map.page},#{map.pageSize}) u left join admin_role_account mid on u.id=mid.account_id and u.user_status=0
left join admin_role r
on r.id=mid.role_id and r.is_deleted=0
left join admin_permission_role pmid on r.id=pmid.admin_role_id
left join admin_permission per on pmid.admin_permissions_id=per.id
ORDER BY u.gmt_update desc
然后统计个数的时候只要在下面去掉limit;select选择的时候select distinct(主建用户) ,这样能知道有几个,返回的是数组,你也可以继续 select count(distinct(orgin.id)) as total
下面就和查询列表一样啦。