<?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="org.dromara.system.mapper.score.ScoreDataMapper">

    <resultMap type="org.dromara.system.domain.score.vo.ScoreDataVo" id="ScoreDataVoResult">
        <result property="scoreId"    column="score_id"    />
        <result property="scoreTitle"    column="score_title"    />
        <result property="scoreTime"    column="score_time"    />
        <result property="scoreClassId"    column="score_class_id"    />
        <result property="scoreSort"    column="score_sort"    />
        <result property="scoreClassName"    column="score_class_name"    />
        <result property="privacyType"    column="privacy_type"    />
        <result property="scoreType"    column="score_type"    />
        <collection property="scoreDataStudentVoList"    javaType="java.util.List"           resultMap="ScoreDataDetailsResult" />
        <collection property="scoreDataMfVoList"   javaType="java.util.List"           resultMap="ScoreDataMfResult"   />
    </resultMap>

    <resultMap type="org.dromara.system.domain.score.vo.ScoreDataStudentVo" id="ScoreDataDetailsResult">
        <result property="scoreStudentId"    column="sds_score_student_id"    />
        <result property="scoreId"    column="sds_score_id"    />
        <result property="scoreSort"    column="sds_score_sort"    />
        <result property="scoreDataName"    column="sds_score_data_name"    />
        <result property="scoreDataNameId"    column="sds_score_data_name_id"    />
        <result property="zongfen"    column="sds_zongfen"    />
        <result property="avg"    column="sds_avg"    />
        <collection property="scoreDataDetailVoList"   javaType="java.util.List"           resultMap="ScoreDataDetailResult"   />
    </resultMap>

    <resultMap type="org.dromara.system.domain.score.vo.ScoreDataDetailVo" id="ScoreDataDetailResult">
        <result property="scoreDetailId"    column="sdd_score_detail_id"    />
        <result property="scoreId"    column="sdd_score_id"    />
        <result property="xueke"    column="sdd_xueke"    />
        <result property="score"    column="sdd_score"    />
        <result property="scoreDataName"    column="sdd_score_data_name"    />
        <result property="scoreDataNameId"    column="sdd_score_data_name_id"    />
    </resultMap>
    <resultMap type="org.dromara.system.domain.score.vo.ScoreDataMfVo" id="ScoreDataMfResult">
        <result property="mfId"    column="sdm_mf_id"    />
        <result property="scoreId"    column="sdm_score_id"    />
        <result property="xueke"    column="sdm_xueke"    />
        <result property="manfen"    column="sdm_manfen"    />
        <result property="avg"    column="sdm_avg"    />
    </resultMap>
    <select id="classRank" parameterType="org.dromara.system.domain.score.ScoreData" resultMap="ScoreDataVoResult">
        SELECT
        (@INDEX := @INDEX + 1) AS `score_sort`,
        d.score_class_name
        FROM
        score_data d,(select @INDEX:=0) init
        <where>
            <if test="scoreType != null  and scoreType != ''"> and d.score_type = #{scoreType}</if>
            <if test="scoreTime != null "> and d.score_time = #{scoreTime}</if>
            <if test="scoreClassName != null  and scoreClassName != ''"> and d.score_class_name like concat( #{scoreClassName}, '%')</if>
        </where>
         order by d.avg  DESC
    </select>


    <select id="avgRank" parameterType="org.dromara.system.domain.score.ScoreData" resultMap="ScoreDataVoResult">
        select
        d.score_id,
        d.avg
        FROM
        score_data d
        <where>
            <if test="scoreType != null  and scoreType != ''"> and d.score_type = #{scoreType}</if>
            <if test="scoreTime != null "> and d.score_time = #{scoreTime}</if>
            <if test="scoreClassName != null  and scoreClassName != ''"> and d.score_class_name like concat( #{scoreClassName}, '%')</if>
            <if test="scoreClassId != null  "> and d.score_class_id = #{scoreClassId}</if>
        </where>
    </select>


</mapper>
