问题

pageHelper的原理是默认执行分页中对应数量条数的sql，所以在mybatis的1对多查询中，如果直接书写，会查询出少于你主表条数的结果，产生问题，并且有些查询条件需要集成在多表这一方，，之前在博文上找了很久，没有啥具体答案，参考了mybatis的官方文档，整理了如下示例，直接上代码

主sql语句

<select id="findAllByCondition" resultMap="findAllTrafficEventMap">
        select
            event.*,
            maxHistory.id as trafficEventHistoryId,
            case when ('${judgeStartTime}' != null) then '${judgeStartTime}' else '' end as judgeStartTime,
            case when ('${judgeEndTime}' != null) then '${judgeEndTime}' else '' end as judgeEndTime
        from
            traffic_event event
        inner join
            (select max(id) as id, traffic_event_id from traffic_event_history group by traffic_event_id) maxHistory
        on event.traffic_event_id  = maxHistory.traffic_event_id
        <where>
            <if test="trafficEventType != null">
                event.traffic_event_type = #{trafficEventType}
            </if>
            <if test="isRelieved != null">
                and event.is_relieved = #{isRelieved}
            </if>
            <if test="checkStatus != null">
                and event.check_status = #{checkStatus}
            </if>
        </where>
    </select>

多方sql语句

<select id="selectJudgeTimes" resultMap="JudgeTimesMap" parameterType="java.util.Map">
        SELECT
            judgeTime.*
        FROM
            traffic_event_judge_time judgeTime
        <where>
            judgeTime.traffic_event_history_id = #{trafficEventHistoryId}
           <if test="judgeStartTime != null and judgeStartTime != ''">
                and judgeTime.judge_time :: timestamp >= #{judgeStartTime} :: timestamp
            </if>
            <if test="judgeEndTime != null and judgeEndTime != ''">
                and judgeTime.judge_time :: timestamp &lt;= #{judgeEndTime} :: timestamp
            </if>
        </where>
    </select>

两者的映射关系

 <resultMap id="findAllTrafficEventMap" type="com.bxt.event.po.TrafficEventPO">
        <result column="traffic_event_id"               property="trafficEventId"/>
        <result column="drc_event_report_time"          property="drcEventReportTime"/>
        <collection property="judgeTimes"               ofType="com.bxt.event.po.JudgeTimePO"
                    select="selectJudgeTimes" column="{trafficEventHistoryId=trafficEventHistoryId, judgeStartTime=judgeStartTime, judgeEndTime=judgeEndTime}">
        </collection>
    </resultMap>

其中trafficEventHistoryId，judgeStartTime，judgeEndTime三者属于多方的查询条件

参数含义讲解：

<!--property：对应实体类中的参数名称-->
<!--column：需要往子表传递的字段-->
<!--javaType：该参数类型-->
<!--ofType：该参数泛型-->
<!--select：子查询id名称-->
<collection property="" column="" ofType="" javaType="" select="" />