MyBatis一对多嵌套list主表分页limit

废话少说，直接上图上代码

如图，1、任务列表，2，每条任务包含一个任务承担人列表；

现在想对任务列表（主表projects_tasks）进行分页查询，一般直接在SQL语句后加上limit即可，但是在嵌套列表查询中，分页参数对应的是left join后的限制，变成了对任务承担人分页限制，并不是我们预期只对主任务列表的分页限制。那应该怎么办呢，请直接看下面xml最后加粗的那一句。加上IN限制，即可实现我们想要的结果。快试试吧，不明白的可以留言，我看到会回复，不过很少上

Projects_TasksMapper.xml

<resultMap id="BaseResultMap2" type="org.macfeng.beans.ProjectTaskBean">
  <id column="taskId" jdbcType="INTEGER" property="taskId" />
  <id column="milestoneId" jdbcType="INTEGER" property="milestoneId" />
  <result column="milestoneName" jdbcType="VARCHAR" property="milestoneName" />
  <result column="taskName" jdbcType="VARCHAR" property="taskName" />
  <result column="create_by" jdbcType="CHAR" property="createById" />
  <result column="createByName" jdbcType="VARCHAR" property="createByName" />
  <result column="intend_start_date" jdbcType="TIMESTAMP" property="intendStartDate" />
  <result column="intend_end_date" jdbcType="TIMESTAMP" property="intendEndDate" />
  <result column="schedule" jdbcType="VARCHAR" property="schedule" />
  <result column="status" jdbcType="VARCHAR" property="statusId" />
  <result column="dic_name" jdbcType="VARCHAR" property="statusName" />
  <result column="description" jdbcType="VARCHAR" property="description" />
  <collection property="responsibleList" resultMap="responsibleResultMap"/>
</resultMap>
<resultMap id="responsibleResultMap" type="org.macfeng.beans.ProjectTaskResponsibleBean">
  <result column="responsibleName" jdbcType="VARCHAR" property="responsibleName" />
  <result column="responsibleId" jdbcType="INTEGER" property="responsibleId" />
</resultMap>
<select id="selectProjectTask" parameterType="org.macfeng.beans.ProjectTaskBean" resultMap="BaseResultMap2">
  select distinct pt.id taskId,pt.milestone_id milestoneId, pm.title milestoneName, pt.title taskName,pt.create_by, te.chinese_name createByName,
  pt.intend_start_date, pt.intend_end_date,pt.schedule, pt.status,td.dic_name, pt.description ,
  ptr.responsible_id responsibleId,te2.chinese_name responsibleName
  from projects_tasks pt left join projects_milestones pm on pm.id = pt.milestone_id
  left join t_employee te on te.employee_id = pt.create_by
  left join t_dictionary td on pt.status=td.dic_cd and td.dic_cls=11
  left join projects_tasks_responsible ptr on ptr.task_id = pt.id
  left join t_employee te2 on te2.employee_id = ptr.responsible_id
  where 1=1
  <if test="projectTaskBean.projectId != null">
    and
    pt.project_id=#{projectTaskBean.projectId}
  </if>
  <if test="projectTaskBean.milestoneId != null">
    and
    pt.milestone_id=#{projectTaskBean.milestoneId}
  </if>
  <if test="projectTaskBean.taskName != null">
    and
    pt.title like %${projectTaskBean.taskName}%
  </if>
  <if test="projectTaskBean.createById != null">
    and
    ptr.responsible_id=#{projectTaskBean.createById}
  </if>
  <if test="projectTaskBean.intendStartDate != null">
    and
    pt.intend_start_date>=#{projectTaskBean.intendStartDate}
  </if>
  <if test="projectTaskBean.intendEndDate != null">
    and
    pt.intend_end_date &lt;= #{projectTaskBean.intendEndDate}
  </if>
  <if test="projectTaskBean.statusId != null">
    and
    pt.status= #{projectTaskBean.statusId}
  </if>
  <if test="startNum != null and rowCount != null">
  and  pt.id IN (SELECT temp.ptid from (SELECT id ptid FROM projects_tasks limit #{startNum}, #{rowCount}) AS temp)
</select>

 

参考：

https://www.cnblogs.com/mobingfeng/p/12711168.html