LEFT JOIN一对多引起的重复问题

Question

问题阐述：

<sql id="Left_Child">
SELECT
    s.id,
    s.student_number,
    s.school_enrollment_number,
    s.school_id,
    s.student_name,
    s.sex,
    s.politic_countenance,
    s.nation,
    s.id_card,
     t.school_name,
    t.school_code,
    st.entrance_date,
    st.mailing_address,
    cu.guardian_address,
    cu.guardian_name,
    cu.guardian_telephone
FROM t_sch_student AS s
LEFT JOIN t_sch_school t on s.school_id = t.id and t.valid_flag = '1'
LEFT JOIN t_sch_student_status st on st.student_id = s.id and st.valid_flag = '1'
LEFT JOIN t_sch_student_custody cu on cu.student_id = s.id and cu.valid_flag = '1'
</sql>

t_sch_student_status、学生学籍表，t_sch_student_custody　学生监护信息表，，和学生基本表都是一对多的关系，如果用了left　join，在导出excel的时候，一个学生有几个监护人，有几个学籍，就会导出几条数据，

但是如果不用left　join，而是用　in，则导出无数据，SQL和结果如下：

<select id="selectAllStudentBaseAndChildInfo" resultType="com.rdp.project.manage.domain.SchStudentDO" resultMap="BaseResultMap">
    <include refid="Left_Child"/>
    <where>
        s.valid_flag = '1'
        <if test="r.studentNumber != null  and r.studentNumber != ''">
            and s.student_number like concat('%', #{r.studentNumber}, '%')
        </if>
        <if test="r.schoolId != null  and r.schoolId != ''">and s.school_id = #{r.schoolId}</if>
        <if test="r.studentName != null  and r.studentName != ''">
            and s.student_name like concat('%', #{r.studentName}, '%')
        </if>
        <if test="r.sex != null  and r.sex != ''">
            and s.sex = #{r.sex}
        </if>
        <if test="r.politicCountenance != null  and r.politicCountenance != ''">
            and s.politic_countenance = #{r.politicCountenance}
        </if>
        <if test="r.idCard != null  and r.idCard != ''">and s.id_card = #{r.idCard}</if>
         <if test="r.entranceDate != null  and r.entranceDate != ''">
            and s.id in (select st.student_id from  t_sch_student_status as st where
            st.valid_flag = '1' and st.entrance_date = #{r.entranceDate})</if>
        <if test="r.mailingAddress != null  and r.mailingAddress != ''">
            and s.id in (select st.student_id from  t_sch_student_status as st where
            st.valid_flag = '1' and st.mailing_address = #{r.mailingAddress})</if>
        <if test="r.guardianAddress != null  and r.guardianAddress != ''">
            and s.id in (select cu.student_id from  t_sch_student_custody as cu where
            cu.valid_flag = '1' and cu.guardian_address = #{r.guardianAddress})</if>
        <if test="r.guardianName != null  and r.guardianName != ''">
            and s.id in (select cu.student_id from  t_sch_student_custody as cu where
            cu.valid_flag = '1' and cu.guardian_name = #{r.guardianName})</if>
        <if test="r.guardianTelephone != null  and r.guardianTelephone != ''">
            and s.id in (select cu.student_id from  t_sch_student_custody as cu where
            cu.valid_flag = '1' and cu.guardian_telephone = #{r.guardianTelephone})</if>
        <if test="r.schoolIds != null">
            and school_id in
            <foreach collection="r.schoolIds" item="schoolId" open="("
                     separator="," close=")">
                #{schoolId}
            </foreach>
        </if>
    </where>
</select>

结果：字段导出无数据

Answer 1

你试试用inner join