来自数据库的数据不正确

Question

我尝试制作学生成绩管理系统。当我试图根据每个学生在考试中获得的总分来确定位置时，我遇到了问题。在我的输出中，我得到 1st（获得标记 450）、2nd（获得标记 449）、4th（获得标记 448）。缺少第 3 个位置。5、10 和其他一些位置丢失。但是我在我的代码中没有发现任何问题。

if (!function_exists('get_position_in_exam')) {

function get_position_in_exam($school_id, $exam_id, $class_id, $section_id, $mark) {

    $ci = & get_instance();

    $sql = "SELECT id, total_obtain_mark, FIND_IN_SET( total_obtain_mark,(

            SELECT GROUP_CONCAT( total_obtain_mark  ORDER BY total_obtain_mark DESC ) 

            FROM exam_results WHERE school_id = $school_id AND exam_id = $exam_id AND class_id = $class_id AND section_id = $section_id ))

            AS rank 

            FROM exam_results

            WHERE school_id = $school_id AND exam_id = $exam_id AND class_id = $class_id AND section_id = $section_id AND total_obtain_mark = $mark"; 

    $rank =  @$ci->db->query($sql)->row()->rank; 

    if($mark == 0){

        return '--'; 

    }

    if($rank == 1){

        return $rank.'st';

    }elseif($rank == 2){

       return $rank.'nd'; 

    }elseif($rank == 3){

       return $rank.'rd'; 

    }elseif($rank > 3 ){

        return $rank.'th';         

    }else{

        return '--'; 

    }

}

}

Answer 1

您的子查询中需要不同的 total_obtain_markGROUP_CONCAT( distinct total_obtain_mark  ORDER BY total_obtain_mark DESC )"SELECT id, total_obtain_mark, FIND_IN_SET( total_obtain_mark,(   SELECT GROUP_CONCAT( distinct total_obtain_mark  ORDER BY total_obtain_mark DESC )    FROM exam_results WHERE school_id = $school_id AND exam_id = $exam_id AND class_id = $class_id AND section_id = $section_id ))   AS rank    FROM exam_results   WHERE school_id = $school_id AND exam_id = $exam_id AND class_id = $class_id AND section_id = $section_id AND total_obtain_mark = $mark";否则，如果您有一些相同等级的学生，您将跳过位置