PageHelper会过滤掉重复的数据,导致mybaits查询出来的数据多余实际返回的记录数,但是返回的总记录数不变
查询语句:
<select id="getEmpSalaryList" parameterType="com.gfsecurity.parameterEntity.SalaryParam" resultMap="BaseResultSalary">
SELECT e.id,e.emp_num,e.emp_realname,e.project_name,e.phone,e.id_card,e.hiredate,e.depart_id, s.* FROM tb_employee e
LEFT JOIN tb_emp_salary s ON e.id=s.emp_id
<where>
e.is_del=0
<if test="empId !=null">
and s.emp_id=#{empId}
</if>
<if test="deptId !=null">
and s.deptid=#{deptId}
</if>
<if test="importSalary != null">
group by e.id
</if>
order by s.create_time desc
</where>
resultMap:
<resultMap id="BaseResultMap" type="com.gfsecurity.pojo.TbEmployee">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="uuid" jdbcType="VARCHAR" property="uuid" />
<result column="token" jdbcType="VARCHAR" property="token" />
<result column="emp_num" jdbcType="VARCHAR" property="empNum" />
<result column="emp_username" jdbcType="VARCHAR" property="empUsername" />
<result column="emp_realname" jdbcType="VARCHAR" property="empRealname" />
<result column="emp_password" jdbcType="VARCHAR" property="empPassword" />
</resultMap>
<resultMap extends="BaseResultMap" id="BaseResultSalary" type="com.gfsecurity.pojo.TbEmployee">
<association javaType="com.gfsecurity.pojo.TbEmpSalary" property="empSalary">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="emp_id" jdbcType="INTEGER" property="empId" />
<result column="hiredate_money" jdbcType="DOUBLE" property="hiredateMoney" />
<result column="absenteeism" jdbcType="DOUBLE" property="absenteeism" />
<result column="food_allowance" jdbcType="DOUBLE" property="foodAllowance" />
<result column="phone_allowance" jdbcType="DOUBLE" property="phoneAllowance" />
<result column="traffic_allowance" jdbcType="DOUBLE" property="trafficAllowance" />
<result column="work_overtime" jdbcType="DOUBLE" property="workOvertime" />
<result column="performance" jdbcType="DOUBLE" property="performance" />
<result column="other_money" jdbcType="DOUBLE" property="otherMoney" />
<result column="water_electricity_deductions" jdbcType="DOUBLE" property="waterElectricityDeductions" />
</association>
</resultMap>
查询出来数据有重复的id导致pageHelper过滤掉了重复的数据
解决方法:
1.使id不重复
2.可以不用id就把id去掉
3.如果其他数据重复导致pageHelper过滤掉数据就加上<id/>
我是出现id重复导致数据过滤,所以我把id去掉了
查询语句变成:
<select id="getEmpSalaryList" parameterType="com.gfsecurity.parameterEntity.SalaryParam" resultMap="BaseResultSalary">
SELECT e.emp_num,e.emp_realname,e.project_name,e.phone,e.id_card,e.hiredate,e.depart_id, s.* FROM tb_employee e
LEFT JOIN tb_emp_salary s ON e.id=s.emp_id
<where>
e.is_del=0
<if test="empId !=null">
and s.emp_id=#{empId}
</if>
<if test="deptId !=null">
and s.deptid=#{deptId}
</if>
<if test="importSalary != null">
group by e.id
</if>
order by s.create_time desc
</where>
把e.id去掉了