多表联查导致前端数据条数显示异常，将关联查询改为嵌套查询

      我用若依框架最近做项目时遇到了一个bug，我后端明明传给前端就3条数据，但是前端却显示共有7条数据，然后我去网上查看一些类似的问题，然后发现我的resultMap存在的一些一对一、一对多关系都是用的关联查询，改为嵌套查询就没问题了。

出现问题的页面

原xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.duty.mapper.DutyMapper">

    <resultMap type="com.ruoyi.duty.vo.DutyVO" id="DutyResult">
        <id property="dutyId"    column="duty_id"    />
        <result property="dutyStatus"    column="duty_status"    />
        <result property="dutyName"    column="duty_name"    />
        <result property="scheduId"    column="schedu_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="dutyPerson"    column="duty_person"    />
        <result property="dutyTime"    column="duty_time"    />
        <result property="personId"    column="person_id"    />
        <result property="phone"    column="phone"    />
        <result property="dutyFile"    column="duty_file"    />
        <result property="dutyNote"    column="duty_note"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />

        <association property="sysUser" javaType="com.ruoyi.common.core.domain.entity.SysUser" column="create_by" autoMapping="true">
            <result property="userName"  column="user_name"/>
        </association>

        <association property="sysDept" javaType="com.ruoyi.common.core.domain.entity.SysDept" column="dept_id" autoMapping="true">
            <result property="deptName" column="dept_name"/>
        </association>

        <association property="schedu" javaType="com.ruoyi.duty.domain.Schedu" column="schedu_id" autoMapping="true">
            <id property="scheduId" column="schedu_id"/>
            <result property="scheduName" column="schedu_name"/>
        </association>

        <collection property="dutyPersonList" ofType="com.ruoyi.duty.domain.DutyPerson" javaType="List" column="duty_id" autoMapping="true">
            <id property="dutyPerId" column="duty_per_id"/>
            <result property="personId" column="dpPerson_id"/>
            <result property="dutyId" column="duty_id"/>
            <association property="user" javaType="com.ruoyi.common.core.domain.entity.SysUser" column="person_id" autoMapping="true">
                <result property="userName" column="su1User_name"/>
            </association>
        </collection>
    </resultMap>


    <select id="selectDutyList" parameterType="com.ruoyi.duty.domain.Duty" resultMap="DutyResult">
        select duty.duty_id, duty_status, duty_name, duty.schedu_id, duty.dept_id, duty_person, duty_time,
        duty.person_id,duty_file, duty_note, duty.create_by, duty.create_time,schedu_name,
        sd.dept_name,su.user_name,su.phonenumber,duty_per_id,dp.person_id as dpPerson_id,su1.user_name as su1User_name
        from duty
        left join schedu
        on duty.schedu_id=schedu.schedu_id
        left join duty_person as dp
        on duty.duty_id=dp.duty_id
        left join sys_user as su
        on duty.create_by=su.user_id
        left join sys_dept as sd
        on duty.dept_id=sd.dept_id
        left join sys_user as su1
        on su1.user_id=dp.person_id
        <where>
            <if test="dutyStatus != null  and dutyStatus != ''"> and duty_status = #{dutyStatus}</if>
            <if test="dutyName != null  and dutyName != ''"> and duty_name like concat('%', #{dutyName}, '%')</if>
            <if test="scheduId != null and scheduId != '' "> and duty.schedu_id = #{scheduId}</if>
            <if test="deptId != null and deptId !=''"> and duty.dept_id = #{deptId}</if>
        </where>
    </select>

</mapper>

我这个联查的表有点多，包含了一个一对多（collection）的关系，而我用的是关联查询，debug查出来的条数是和副表对应的，所以我将sql拆分，然后resultMap采用嵌套查询

改过之后的xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.duty.mapper.DutyMapper">

    <resultMap type="com.ruoyi.duty.vo.DutyVO" id="DutyResult">
        <id property="dutyId"    column="duty_id"    />
        <result property="dutyStatus"    column="duty_status"    />
        <result property="dutyName"    column="duty_name"    />
        <result property="scheduId"    column="schedu_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="dutyPerson"    column="duty_person"    />
        <result property="dutyTime"    column="duty_time"    />
        <result property="personId"    column="person_id"    />
        <result property="phone"    column="phone"    />
        <result property="dutyFile"    column="duty_file"    />
        <result property="dutyNote"    column="duty_note"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />

        <association property="sysUser" javaType="com.ruoyi.common.core.domain.entity.SysUser" column="create_by" autoMapping="true">
            <result property="userName"  column="user_name"/>
        </association>

        <association property="sysDept" javaType="com.ruoyi.common.core.domain.entity.SysDept" column="dept_id" autoMapping="true">
            <result property="deptName" column="dept_name"/>
        </association>

        <association property="schedu" javaType="com.ruoyi.duty.domain.Schedu" column="schedu_id" autoMapping="true">
            <id property="scheduId" column="schedu_id"/>
            <result property="scheduName" column="schedu_name"/>
        </association>

        <collection property="dutyPersonList" ofType="com.ruoyi.duty.domain.DutyPerson"
                    javaType="List" column="duty_id" autoMapping="true" select="selectDutyPerson">
        </collection>
    </resultMap>

    <resultMap id="DutyResultInclude" type="com.ruoyi.duty.domain.DutyPerson" autoMapping="true">
        <id property="dutyPerId" column="duty_per_id"/>
        <result property="personId" column="person_id"/>
        <result property="dutyId" column="duty_id"/>
        <association property="user" javaType="com.ruoyi.common.core.domain.entity.SysUser" column="person_id" autoMapping="true">
            <result property="userName" column="user_name"/>
        </association>
    </resultMap>

    <select id="selectDutyList" parameterType="com.ruoyi.duty.domain.Duty" resultMap="DutyResult">
        select duty.duty_id, duty_status, duty_name, duty.schedu_id, duty.dept_id, duty_person, duty_time,
        duty.person_id,duty_file, duty_note, duty.create_by, duty.create_time,schedu_name,
        sd.dept_name,su.user_name,su.phonenumber
        from duty
        left join schedu
        on duty.schedu_id=schedu.schedu_id
        left join sys_user as su
        on duty.create_by=su.user_id
        left join sys_dept as sd
        on duty.dept_id=sd.dept_id
        <where>
            <if test="dutyStatus != null  and dutyStatus != ''"> and duty_status = #{dutyStatus}</if>
            <if test="dutyName != null  and dutyName != ''"> and duty_name like concat('%', #{dutyName}, '%')</if>
            <if test="scheduId != null and scheduId != '' "> and duty.schedu_id = #{scheduId}</if>
            <if test="deptId != null and deptId !=''"> and duty.dept_id = #{deptId}</if>
        </where>
    </select>

    <select id="selectDutyPerson" parameterType="com.ruoyi.duty.domain.Duty" resultMap="DutyResultInclude">
        select duty_person.*,sys_user.user_name from  duty_person
        left join sys_user
        on sys_user.user_id=duty_person.person_id
        where duty_id=#{duty_id}
    </select>

</mapper>

我将原sql拆分成了两部分，将新的sql嵌套到<collection>中，就解决了显示条数与实际条数不匹配的问题

成功之后的查询条数

可以看到从原来的7条变成了3条，问题成功解决!