<?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.qmth.distributed.print.business.mapper.TeachStudentMapper">

    <select id="findTeachStudentPage"
            resultType="com.qmth.distributed.print.business.bean.result.TeachStudentResult">
        SELECT
            ts.id AS id,
            bs.student_name AS studentName,
            bs.student_code AS studentCode,
            bce.college_name AS belongOrgName,
            bm.name AS majorName,
            bc.clazz_name AS clazzName
        FROM
            teach_student ts
                LEFT JOIN
            basic_student bs ON ts.student_id = bs.id
                LEFT JOIN
            basic_college bce ON bs.belong_org_id = bce.id
                LEFT JOIN
            basic_major bm ON bs.major_id = bm.id
                LEFT JOIN
            basic_clazz bc ON bs.clazz_id = bc.id
        <where>
            <if test="schoolId != null and schoolId != ''">
                AND ts.school_id = #{schoolId}
            </if>
            <if test="teachClazzId != null and teachClazzId != ''">
                AND ts.teach_clazz_id = #{teachClazzId}
            </if>
            <if test="studentInfo != null and studentInfo != ''">
                AND (bs.student_name LIKE CONCAT('%',#{studentInfo},'%') OR bs.student_code LIKE CONCAT('%',#{studentInfo},'%'))
            </if>
        </where>
        ORDER BY id
    </select>
</mapper>