<?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.ym.mec.education.mapper.ClassGroupStudentMapperMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.ym.mec.education.entity.ClassGroupStudentMapper">
        <id column="id_" property="id"/>
        <result column="class_group_id_" property="classGroupId"/>
        <result column="user_id_" property="userId"/>
        <result column="create_time_" property="createTime"/>
        <result column="status_" property="status"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id_, class_group_id_, user_id_, create_time_, status_
    </sql>
    <sql id="Column_List">
        class_group_student_mapper.id_, class_group_student_mapper.class_group_id_, class_group_student_mapper.user_id_, class_group_student_mapper.create_time_, class_group_student_mapper.status_
    </sql>

    <select id="selectStudentPage" parameterType="com.ym.mec.education.req.ClassGroupReq"
            resultType="com.ym.mec.education.resp.ClassStudentResp">

        SELECT s.real_name_ as name,s.avatar_ as avatar,m.class_group_id_ as classGroupId,s.id_ as userId,c.music_group_id_ as musicGroupId
        from class_group_student_mapper m

        LEFT JOIN class_group c on m.class_group_id_ = c.id_

        LEFT JOIN sys_user s on m.user_id_ = s.id_

        where c.music_group_id_ = #{musicGroupId}


    </select>

    <select id="selectPageByCondition" parameterType="com.ym.mec.education.req.ClassGroupReq" resultMap="BaseResultMap">
        SELECT class_group_student_mapper.* FROM class_group_student_mapper LEFT JOIN class_group ON class_group_student_mapper.class_group_id_ = class_group.id_
	    LEFT JOIN `subject` ON `subject`.id_ in (class_group.subject_id_list_)
	    where
	    class_group_student_mapper.class_group_id_ = #{query.groupId}
	    and class_group_student_mapper.status_ in ('NORMAL', 'LEAVE')
	    <if test="query.subjectId != null">
            and subject.id_ = #{query.subjectId}
        </if>
    </select>

</mapper>
